How to use sp_executesql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to use sp_executesql

Author

Topic

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 21:09:41

I have a table with Table_Name and Column_name Populated. I need to write a dynamic query to populate the third column “Populated”.

Table_Name Column_name Populated
Lists.......ListID............22
Lists.......ItemName..........10
Lists.......ItemAbbreviation..17
Lists.......ItemDescription...5

I have been following examples but am hyaving a problem with my
EXEC sp_executesql @SQL, N'@tbl sysname', @tbl statement. The BOL was
not clear on what the parameters are it is expecting. When i run the
code i get this error:

Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type
'ntext/nchar/nvarchar'.

1. Why is it asking for '@statement' ? How do i correct the error?
2. What is N'@tbl sysname'? I see N' used in the example shown in
Using sp_executesql
(http://msdn2.microsoft.com/en-us/library/ms175170.aspx) But i have no
idea why it is used or what it does?

--Create a crsor scoped to the batch, stored procedure, or trigger
--in which the cursor was created. The cursor name is only valid
--within this scope. Populate the cursor from #Temp_Table.
DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Table_name, Column_name FROM #Temp_Table

--Opens a Transact-SQL server cursor and populates the cursor by
--executing the Transact-SQL statement specified on the DECLARE
--CURSOR or SET cursor_variable statement.
OPEN cur

WHILE 1 = 1
BEGIN

--Retrieves a specific row from a Transact-SQL server cursor.
declare @tbl varchar(50), @col varchar(50), @Sql varchar(1000)
FETCH cur INTO @tbl, @col

--Returns the status of the last cursor FETCH statement issued

--against any cursor currently opened by the connection.
--0 = The FETCH statement was successful.
IF @@fetch_status <> 0
--All of the rows have been fetched from the cursor.
BREAK

set @Sql = 'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + @tbl +
' WHERE Len(' + @col + ')<> 0)

WHERE Table_Name = @tbl'
EXEC sp_executesql @SQL, N'@tbl sysname', @tbl
END

DEALLOCATE cur

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-25 : 21:17:38

declare the @SQL as nvarchar

KH
[spoiler]Time is always against us[/spoiler]

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-11-25 : 21:18:27

Should like this:

EXEC sp_executesql @SQL, N'@tbl sysname', @tbl = statement

Books online has samples.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 21:46:14

The solutions corrected the error. Thank you.

But it appears not to be stepping through the cur. The Populated column has the same value for all rows. 51 is the correct value for Notes, ListID (the PK) should be 159. Is the a way for me to view teh contents of cur or check that it is being stepped through?

51 lists ListID 1
51 lists ListNameID 2
51 lists ListOrder 3
51 lists ItemName 4
51 lists ItemAbbreviation 5
51 lists ItemDescription 6
51 lists Notes 7

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-25 : 22:01:10

please post your query here

KH
[spoiler]Time is always against us[/spoiler]

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-25 : 22:14:05

My mistake. The select * from #Temp_Table at the end was looking at ols values. I mually set Populated to zeros and ran the following code. None of the zeros had been updated.

0 lists ListID 1
0 lists ListNameID 2
0 lists ListOrder 3
0 lists ItemName 4
0 lists ItemAbbreviation 5
0 lists ItemDescription 6
0 lists Notes 7

Here is my code:

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Table_name, Column_name FROM #Temp_Table

OPEN cur

WHILE 1 = 1
BEGIN

declare @tbl varchar(50), @col varchar(50), @Sql nvarchar(1000)
FETCH cur INTO @tbl, @col

IF @@fetch_status <> 0
--All of the rows have been fetched from the cursor.
BREAK

set @Sql = 'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + @tbl +
' WHERE Len(' + @col + ')<> 0)
WHERE Table_Name = @tbl'
EXEC sp_executesql @SQL, N'@tbl sysname', @tbl = statement
END

DEALLOCATE cur

select * from #Temp_Table

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-26 : 01:55:41

I need to set the value of the Populated column. The file starts out populated as follows
Populated, Table_name, Column_name, ordinal_position
0 lists ListID 1
0 lists ListNameID 2
0 lists ListOrder 3
0 lists ItemName 4
0 lists ItemAbbreviation 5
0 lists ItemDescription 6
0 lists Notes 7

Running the code should Populate the “Populate” column. I know the value for ListID should be 159 and Notes should be 51.

Running the code as is leaves the Populated column with all zeros

If I remove WHERE Table_Name = @tbl' the Populated column is changed to all 51’s.

If I change the WHERE to WHERE Column_Name = @col' I get this error
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@col".
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@col".
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@col".
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@col".

Can some one explain what is happening here and suggest a fix?

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Table_name, Column_name FROM #Temp_Table

OPEN cur

WHILE 1 = 1
BEGIN

declare @tbl varchar(50), @col varchar(50), @Sql nvarchar(1000)
FETCH cur INTO @tbl, @col

IF @@fetch_status <> 0
--All of the rows have been fetched from the cursor.
BREAK

set @Sql = 'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + @tbl + ' WHERE Len(' + @col + ')<> 0) WHERE Table_Name = @tbl'

-- set @Sql = 'Update #temp_table
-- set populated = (SELECT COUNT(*) FROM ' + @tbl +
-- ' WHERE Len(' + @col + ')<> 0)
-- WHERE Table_Name = @tbl'
EXEC sp_executesql @SQL, N'@tbl sysname', @tbl = statement

FETCH cur INTO @tbl, @col

END

CLOSE cur
DEALLOCATE cur

select * from #Temp_Table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-11-26 : 02:25:33

www.sommarskog.se/dynamic_sql.com

Madhivanan

Failing to plan is Planning to fail

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-26 : 15:41:26

I have read the article The Curse and Blessings of Dynamic SQL (http://www.sommarskog.se/dynamic_sql.html). I am new t SQL and I am obviously missing the finer points.

I have #Temp_table containing this
Populated, Table_Name, Column_Name, Ordinal_Position
0 lists ListID 1
0 lists ListNameID 2
0 lists ListOrder 3
0 lists ItemName 4
0 lists ItemAbbreviation 5
0 lists ItemDescription 6
0 lists Notes 7

I know the Populated values for ListID should be 159 and Notes should be 51.

When I run my code I get this error:

Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@col".
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@col".
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@col".
Msg 137, Level 15, State 2, Line 2
Must declare the scalar variable "@col".

I am told that @col is out of scope for the dynamic sql. Apparently my sp_executesql statement is incorrect.

set @Sql = 'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + @tbl + ' WHERE Len(' + @col + ')<> 0) WHERE column_Name = @col'

EXEC sp_executesql @SQL, N'@tbl sysname', @tbl = statement

1. The article explains that the first parameter @stmt is mandatory, and contains a batch of one or more SQL statements. That’s clear.

2. The second parameter @params is optional, but you will use it 90% of the time. @params declares the parameters that you refer to in @stmt. I assume then that @tbl and @col must be listed.

The code I have that seems to be correct expresses @tbl twice? N'@tbl sysname' looks like a parameter and data type. What does @tbl = statement do?

I assume @col must be added to this list. I have tried different forms of EXEC sp_executesql @SQL, N'@tbl sysname', @tbl = statement, N'@col = @col' but always get this error.

Msg 119, Level 15, State 1, Line 19
Must pass parameter number 4 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.

I obviously don’t understand how the syntax is supposed to be constructed. I could really use a clear explanation and an example.

Here is my dynamic sql code.

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Table_name, Column_name FROM #Temp_Table

OPEN cur

WHILE 1 = 1
BEGIN

declare @tbl varchar(50), @col varchar(50), @Sql nvarchar(1000)
FETCH cur INTO @tbl, @col

IF @@fetch_status <> 0
--All of the rows have been fetched from the cursor.
BREAK

set @Sql = 'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + @tbl + ' WHERE Len(' + @col + ')<> 0) WHERE column_Name = @col'

EXEC sp_executesql @SQL, N'@tbl sysname', @tbl = statement

FETCH cur INTO @tbl, @col

END

CLOSE cur
DEALLOCATE cur

select * from #Temp_Table

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-26 : 16:30:18

quote:
Originally posted by kirknew2SQL
set @Sql = 'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + @tbl + ' WHERE Len(' + @col + ')<> 0) WHERE column_Name = @col'

Strange that in the first use of @col in the statement above (2nd line, 10th character) you correctly concatenated the variable's content, but in the second (at the end) you didn't.

Change the second use to


'WHERE column_name = ''' + @col + '''

and try again.

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-26 : 17:05:42

I think this is the correction you wanted me to make. It is not exactly as you suggested because the quotes did not match up and i was getting aan error when i ran the check. The check does not find any errors with this syntax. But when i run the cod i get this error:

Msg 207, Level 16, State 1, Line 2
Invalid column name 'ListID'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'ListOrder'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'ItemAbbreviation'.
Msg 207, Level 16, State 1, Line 2
Invalid column name 'Notes'.

The data in #Temp_table is:
Populated, Table_name, Column_name, Ordinal_position
0 lists ListID 1
0 lists ListNameID 2
0 lists ListOrder 3
0 lists ItemName 4
0 lists ItemAbbreviation 5
0 lists ItemDescription 6
0 lists Notes 7

Populated from SELECT * FROM INFORMATION_SCHEMA.columns (I added the Populated col.) so i know the values are correct. Do I have more bad syntax come where?

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Table_name, Column_name FROM #Temp_Table

OPEN cur

WHILE 1 = 1
BEGIN

declare @tbl varchar(50), @col varchar(50), @Sql nvarchar(1000)
FETCH cur INTO @tbl, @col

IF @@fetch_status <> 0
--All of the rows have been fetched from the cursor.
BREAK

set @Sql = 'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + @tbl + ' WHERE Len(' + @col + ')<> 0) WHERE column_name = ' + @col + ''

EXEC sp_executesql @SQL, N'@tbl sysname', @tbl = statement

FETCH cur INTO @tbl, @col

END

CLOSE cur
DEALLOCATE cur

select * from #Temp_Table

KenW
Constraint Violating Yak Guru

391 Posts

Posted - 2007-11-27 : 13:49:00

quote:
Originally posted by kirknew2SQL

I think this is the correction you wanted me to make. It is not exactly as you suggested because the quotes did not match up and i was getting aan error when i ran the check.

/snip

set @Sql = 'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + @tbl + ' WHERE Len(' + @col + ')<> 0) WHERE column_name = ' + @col + ''

I told you before what the error is; you're still not correctly concatenating the @col in the WHERE condition.

Maybe this will help: You need to include extra quotes so that the final value is


WHERE column_name = 'whatever_is_in_the_col_variable'

What you're getting now is


WHERE column_name = @col

which is obviously wrong.

You'll need to insert extra quotes until you get the right ones. I'd suggest that, after you build the SQL but before you pass it to EXEC, you PRINT the SQL; this will let you see what you're passing on through EXEC.

Does that help?

kirknew2SQL
Posting Yak Master

194 Posts

Posted - 2007-11-28 : 18:44:36

I appreciate your help. Kept me on the right track.

I did some more research and found how to debug using PRINT statement. (Like I said I am very new to SQL) Then I could see what you were talking about. I corrected the concatination so the update statement is correct.

I also did more research on how to pass the parameters correctly. There was an error there too. I have corrected that too. But I could still use some insight as to why/how the various syntax for passing parameters to the dynamic SQL work.

This works and makes sence based on what I read about correct syntax.
EXEC sp_executesql @SQL, N'@tbl sysname, @col varchar(50)', @tbl, @col

Why does this work, even though the @col paramater was not passed?
EXEC sp_executesql @SQL, N'@tbl sysname', @tbl

Why does this not work?
EXEC sp_executesql @SQL, N'@tbl sysname'

Code that works…

DECLARE cur CURSOR STATIC LOCAL FOR
SELECT Table_name, Column_name FROM #Temp_Table

--Opens a Transact-SQL server cursor.
OPEN cur

WHILE 1 = 1
BEGIN

declare @tbl varchar(50), @col varchar(50), @Sql nvarchar(1000)

FETCH cur INTO @tbl, @col

IF @@fetch_status <> 0
BREAK

set @Sql = 'Update #temp_table
set populated = (SELECT COUNT(*) FROM ' + @tbl + ' WHERE Len(' + @col + ')<> 0) WHERE column_name = ''' + @col + ''''

-- Print @sql
EXEC sp_executesql @SQL, N'@tbl sysname', @tbl

FETCH cur INTO @tbl, @col

END

CLOSE cur
DEALLOCATE cur

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources