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.
| Author |
Topic |
|
mariechristine
Starting Member
27 Posts |
 Posted - 2004-05-21 : 06:33:01
|
| I have the following procedure. When i execute it it's giving me syntax error near '='. When I remove @count it works. So what's wrong with the syntax like this and how to solve it??create PROCEDURE [dbo].[USP_GetFileCount] @tableName nvarchar(50),@colName nvarchar(50), @recordID int, @count int outputAS exec( 'select ' + @count + ' = count(*) from '+ @tableName + 'where ' + @colName + ' = ' + @recordID) |
|
|
gpl
Posting Yak Master
195 Posts |
Posted - 2004-05-21 : 06:55:26
|
| The reason is that an EXEC is in a separate batch to the rest of the procedure, so that the @Count variable doesnt exist.I have seen articles on this site that tell you how to get what you want, but cant think of any right now, search on dynamic sql.Graham |
 |
|
|
SmileyConspiracy
Starting Member
8 Posts |
Posted - 2004-05-21 : 07:09:00
|
| To return a value from dynamic SQL you need to use sp_executeSQL, this should do it.create procedure dbo.USP_GetFileCount @tableName nvarchar(50), @colName nvarchar(50), @recordID int, @count int outputASdeclare @nSQL nvarchar(1000)set @nSQL ='select @Count =count(*) from '+ @tableName + ' where ' + @colName + ' =@recordID'exec sp_executeSQL @nSQL, N'@Count int output, @recordID int', @Count =@Count output, @recordID =@recordID |
|
|
|
|
|
|
|
|
Error in proc WHY???