Tejas's Blog

SQL SERVER: Execute Stored Procedure when SQL SERVER is started

We have a requirements to execute Stored Procedure when SQL SERVER is started/restarted and we need to start some processes. I found that SQL SERVER provides a way to call Stored Procedure when SQL services are restarted.

SQL SERVER provides this SP: “sp_procoption”, which is auto executed every time when SQL SERVER service has been started. I found this SP and it helps me to figure it out the solution for the request as following way. Let me show you how to use it.

Syntax use this SP:

EXEC SP_PROCOPTION     
	@ProcName = 'SPNAME',    
	@OptionName = 'startup',    
	@OptionValue = 'true/false OR on/off'

• @ProcName, should be Stored procedure name which should be executed when SQL SERVER is started. This stored procedure must be in “master” database.
• @OptionName, should be “startup” always.
• @OptionValue, this should be set up to execute this given sp or not. If it is “true/on”, given sp will be execute every time when SQL SERVER is started. If it is “false/off”, it will not.

That’s it,

I hope this is very clear to use this feature.

Reference : Tejas Shah (http://www.SQLYoga.com)

SQL SERVER: Read values from Comma Separated variable

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column.

Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this.

Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.

Example:

I have one procedure which has one parameter VARCHAR(100), which might contains value like '1,5,6,20'. What I need to do is: I need to update rows contains these ID(1,5,6,20). So We need to make query which will update status of these IDs.

SET @xmlIDs =    '<IDs>
                    <ID>' + REPLACE(@str, ',', '</ID><ID>') + '</ID>' +
                '</IDs>'

So this statement will generate XML from VARCHAR value as follows:

<IDs>
  <ID>1</ID>
  <ID>6</ID>
  <ID>7</ID>
  <ID>8</ID>
  <ID>20</ID>
</IDs>

So, Now this is the XML, which can be easily read with SQL SERVER 2005 as:

SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)

This will give me result set as: ( as separate table)

So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.

So my Procedure looks like:

CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
    @str    VARCHAR(100)
AS

DECLARE @XmlIDs XML

SET @xmlIDs =    '<IDs>
                    <ID>' + REPLACE(@str, ',', '</ID><ID>') + '</ID>' +
                '</IDs>'

UPDATE    TableName
SET        Flag = 1
WHERE    ID IN(
                SELECT x.v.value('.','INT')
                FROM @xmlIDs.nodes('/IDs/ID') x(v)
            )

So, it is very easy to read values from Comma separated value.

Let me know if it helps you.