Dynamic SQL: calling procedure loop

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 2008 Forums

Transact-SQL (2008)

Dynamic SQL: calling procedure loop

Author

Topic

Arcesilaus
Starting Member

2 Posts

Posted - 2011-09-24 : 10:30:22

Hi

I'm trying to get a little acquainted with Dynamic SQL and I am currently trying to write a procedure that calls another procedure using a parameter that has the value for a specific field for each record in a table.
I almost succeeded, but the last step gives me an error I do not understand.

What works:

SELECT @Exec = 'SELECT @FieldValue = [' + @Field + '] FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (ORDER BY [' + @Field + '] ASC) FROM [' + @Table + ']) AS t WHERE RowNumber = 6 ', @Params = '@FieldValue nvarchar(255) output'

Now I want the '6' to be a variable "@RowCnt" that is declared as an integer:

SELECT @Exec = 'SELECT @FieldValue = [' + @Field + '] FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (ORDER BY [' + @Field + '] ASC) FROM [' + @Table + ']) AS t WHERE RowNumber = ' + @RowCnt + ' ', @Params = '@FieldValue nvarchar(255) output'

But for some reason it does not work: it gives me the following error:

Conversion failed when converting the varchar value 'SELECT @FieldValue = [Woonplaats] FROM (SELECT *, RowNumber = ROW_NUMBER() OVER (ORDER BY [Woonplaats] ASC) FROM [Cities]) AS t WHERE RowNumber = ' to data type int.

I've tried all combinations of parentheses, but to no avail.
What am I missing here?

Kristen
Test

22859 Posts

Posted - 2011-09-24 : 11:05:36

@RowCnt is an INT, so you can't concatenate it with a "string", you would have to convert it to a string first - e.g.


... WHERE RowNumber = ' + CONVERT(varchar(20), @RowCnt) + ' ',  ...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-24 : 12:08:54

also keep in mind that you cant use EXEC() over this string to get back value of @FieldValue outside. for that you need to use sp_executesql after declaring @FieldValue as an output parameter

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-24 : 12:50:47

I think the O/P is probably doing that? as the SELECT is assigning to @Exec and also @Params = '@FieldValue nvarchar(255) output'

Arcesilaus
Starting Member

2 Posts

Posted - 2011-09-25 : 11:20:59

Indeed, using the 'sp_executesql' to execute the @Exec string and will use the @FieldValue for a next statement.

I guess I had been working for too many consecutive hours and overlooked this basic mistake of putting an int to a statement

With conversion to string, it works as expected . Thanks a lot!

Homo sum: humani nil a me alienum puto (Terence)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources