Dynamic SQL problem

Question

Dynamic SQL problem

Richard Douglas

SSC Journeyman

Points: 75
More actions
November 19, 2004 at 2:48 am

#59805

Hi All, I hope somebody can help me.
I am trying to set a variable inside some dynamic sql.
The follwoing code is in a cursor
SET @cSQL2 = 'SET @iTableRows = (SELECT COUNT(*) FROM '+@cTable+')'
EXECUTE (@cSQL2)
When I try to examine @iTableRows it says it isnt declared (yes it has been declared in the cursor.), I am presuming this is because the variable is trashed when the dynamic batch has been run.
My question is is there any way I can assign this value to something else when executing the dynamic SQL.

Viewing 9 posts - 1 through 8 (of 8 total)

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

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 1

Hi Richard,

Executing @cSQL2 will generate a new spid, unfortunately the way you have declared and used the variable it will only be available during the life of the spid (and thats if you add DECLARE into the sequel string).

You can return @iTableRows into your sproc by doing:

SET @cSQL2 = N'SET @iTableRows = (SELECT COUNT(*) FROM '+@cTable+')'

EXEC sp_executesql @cSQL2 , N'@iTableRows int OUTPUT', @iTableRows OUTPUT

This should get you where you want to be.......

Have fun

Steve

We need men who can dream of things that never were.

Richard Douglas SSC Journeyman Points: 75 More actions · Answer 2

Hi Steve,

I now get the following error.

Server: Msg 214, Level 16, State 2, Procedure sp_executesql, Line 51

Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.

Any ideas why this is?

A colleague of mine suggested that I might want to try and create a temp table and insert the value into that and then drop the table, I know I'll have to create this as a global temporary table to get round the spid thing.

Do you think it is a viable option ?

Thanks again,

Rich

Frank Kalis SSC Guru Points: 111183 More actions · Answer 3

The error message is pretty much self-describing. Change the variable type from varchar (I guess it is) to nvarchar

Here's a complete example how to use the result of sp_executesql. Try and run this in the pubs sample db

DECLARE @stmt nvarchar(4000)

DECLARE @rowcount bigint

DECLARE @table nvarchar(255)

SET @table = 'authors'

SELECT @stmt = 'SELECT @count = COUNT(*) FROM ' + @table

EXEC sp_executesql @stmt, N' @count bigint output', @rowcount OUTPUT

IF @rowcount > 0

BEGIN

SELECT @rowcount AS Anzahl

END

RETURN

...but as always with dynamic sql read this:

http://www.sommarskog.se/dynamic_sql.html

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 4

Hi Richard,

The error you describe is in the initial declaration of @cSQL2, you must have declared it as a varchar(x) - it just needs changing to nvarchar(x) and we should see the problem go away.

Temp tables - don't like expressing opinions, just answering problems I have a definite solution to. I've used temp tables on a number of occaissions, they are very handy, sometimes you can't get around using them - sometimes you can.

I don't really know the impact using temp tables has on the database, so really i'm not the right person to ask. I enjoy the dynamic sql problems though.........

Have fun

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 5

Oh, and regarding your temp table question. That might be a valid alternative. However, don't create it inside the dynamic sql batch. It will be gone since the dynamic batch has its own scope and once it ends, the temp table is also gone.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Ste_P SSCarpal Tunnel Points: 4430 More actions · Answer 6

Frank got in whilst I was writing my reply - bugger.

For a proper answer on temp tables he's your man..........

Steve

We need men who can dream of things that never were.

Frank Kalis SSC Guru Points: 111183 More actions · Answer 7

Don't make me feel

< snip and switch to my online dictionary>

embarrassed

</snip>

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Richard Douglas SSC Journeyman Points: 75 More actions · Answer 8

Thanks guys, I've gone with the temp tables as it was quicker for me to implement rather than test the other code. I will have a look at it when I get some spare time.

Thanks again for your help.

Rich