Execute Contents Of A Column

  • I have a table in SQL Server 2000 which has three columns ...

    Statement, ServerName1, ServerName2

    I am wanting to join together the contents of the three columns and then execute this string. To join the contents of the columns I am using ...

    Select [Statement] + '''' + [ServerName1] + '''' + ', ' + '''' + [ServerName2] + '''' From MyTable

    That statement gives me the following ...

    Exec sp_MYprocedure 'MyServer1', 'MyServer2'

    I am then using the following piece of code to execute one of the rows in the table ...

    declare @sql nvarchar(4000)

    select @sql = (Select Top 1 [Statement] + '''' + [ServerName1] + '''' + ', ' + '''' + [ServerName2] + '''' From MyTable)

    --print @sql

    exec sp_executesql @sql

    The table contains multiple rows, therefore, what is the best way to write a cursor to loop through this table and execute the @sql statement?

    Thanks in advance.

    www.sqlAssociates.co.uk

  • This is a more evolved solution than you need but you'll get the idea :

    IF object_id('Data') > 0

    DROP TABLE dbo.Data

    GO

    IF object_id('Target') > 0

    DROP TABLE dbo.Target

    GO

    IF object_id('Transfers') > 0

    DROP TABLE dbo.Transfers

    GO

    CREATE TABLE dbo.Data (nbr int not null primary key clustered)

    GO

    Insert into dbo.Data (nbr) values (24)

    Insert into dbo.Data (nbr) values (64)

    Insert into dbo.Data (nbr) values (35)

    Insert into dbo.Data (nbr) values (7536)

    Insert into dbo.Data (nbr) values (3)

    Insert into dbo.Data (nbr) values (1)

    Insert into dbo.Data (nbr) values (38)

    Insert into dbo.Data (nbr) values (-14)

    Insert into dbo.Data (nbr) values (-4)

    Insert into dbo.Data (nbr) values (0)

    GO

    CREATE TABLE dbo.Target (nbr int not null primary key clustered, WhereCond varchar(100) not null)

    GO

    CREATE TABLE dbo.Transfers (WhereCond varchar(100) not null primary key clustered)

    GO

    Insert into dbo.Transfers (WhereCond) values (' WHERE nbr 0 and nbr 64')

    GO

    Select * from dbo.Data

    Select * from dbo.Transfers

    Select * from dbo.Target

    GO

    EXEC master.dbo.xp_execresultset 'Select ''Insert into dbo.Target (nbr, WhereCond) Select nbr, '''''' + WhereCond + '''''' from dbo.Data '' + WhereCond As execQuery from dbo.Transfers', 'test'

    GO

    Select * from dbo.Target --everything except 64

    GO

    DROP TABLE dbo.Data

    DROP TABLE dbo.Target

    DROP TABLE dbo.Transfers

  • Hi,

    Thanks for that.  The example works but when I change it to reference my tables it falls over.

     

    As the data volumes will be very small (less than 50 rows) is there anyway this can be placed inside a cursor?

     

     

    Thanks in advance.

    www.sqlAssociates.co.uk

  • Yup... but it's faster to write with xp_execresultset.

    Not really any better solution here as they both have to use some sort of looping so you use whatever you preffer.

  • Hi,

    Do you know where I can find out how to execute the statement below using xp_execresultset?  The code below basically creates a string that contains

    exec sp_Myprocedure 'param1', 'param2'

    How can I then take the @sql and execute it using xp_execresultset?

    declare @sql nvarchar(4000)

    select @sql = (Select Top 1 [Statement] + '''' + [ServerName1] + '''' + ', ' + '''' + [ServerName2] + '''' From MyTable)

    --print @sql

    exec sp_executesql @sql

     

    Thanks for your help, much appreciated.

    www.sqlAssociates.co.uk

  • Please post the table definition, sample data and I'll create the script for you...

  • The table that contains the data is ...

    CREATE TABLE [dbo].[MyTable] (

     [Statement] [varchar] (100) COLLATE Latin1_General_CI_AS NULL ,

     [ServerName] [varchar] (30) COLLATE Latin1_General_CI_AS NULL ,

     [ReplaceValue] [varchar] (30) COLLATE Latin1_General_CI_AS NULL

    ) ON [PRIMARY]

    GO

    The table data is ...

    INSERT INTO IPMDBA_t_ServerName

    VALUES ('EXEC sp_FindReplace', 'ServerName1', 'ServerName2')

    The stored procedure that is called from the data in the table above is in another post I have in this T-SQL forum.  The post is called Find And Replace Script Needs Filtering

     

    Thank you for all your help, its really, really appreciated!

    www.sqlAssociates.co.uk

  • Sorry, the script to insert data into the table should have read ...

     

    INSERT INTO MyTable

    VALUES ('EXEC sp_FindReplace', 'ServerName1', 'ServerName2')

    www.sqlAssociates.co.uk

  • EXEC master.dbo.xp_execresultset 'SELECT Statement + '' '''''' + ServerName + '''''', '''''' + ReplaceValue + '''''''' AS ExecStatement FROM dbo.MyTable', 'DB you want the code executed from'

  • Hi,

    Thanks for that.  I've got an error ....

    Error converting data type varchar to bit.

     

    Any ideas?

    www.sqlAssociates.co.uk

  • Hi,

    I've even tried inserting the entire string into MyTable2, and then running ...

     

    EXEC master.dbo.xp_execresultset 'select * from MyTable2', 'MyDB'

     

    And it returns the following ...

     

    Server: Msg 156, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'EXEC'.

    Server: Msg 170, Level 15, State 1, Line 1

    Line 1: Incorrect syntax near 'IPMDBA'.

    www.sqlAssociates.co.uk

  • The parameters are not all varchars and of variable datatypes???

    You'll have to quote the varchars in the raw data directly and remove the quotes from the select statement (4 per column for each side of the string).

Viewing 12 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply