Need help running dynamic stored procedure please 1

[kiwiCoder]

I have tried running this for three days with no success,any ideas anyone??

CREATE PROCEDURE spGetThisTableField --@ReturnVal as int output,
@strTableName sysname, --Recipes
@strCompareFld as char(50), --RecipeKey
@strSearchValue as int, --9
@strFieldToReturn as char(50) --PackCodeKey
--Select Recipes.packCodeKey from Recipes WHERE Recipes.RecipeKey = 9
AS
declare @SQLCommand VarChar(200)

Set @SQLCommand =('Select '+@strTableName+'.'+@strFieldToReturn+' from '+@strTableName+
'where '+@strTableName+'.'+@strCompareFld+' = 9') --+@strSearchValue))

EXEC (@SQLCommand)
GO

 

[vongrunt]

Write ' where' instead of 'where'.
Btw use varchar type for @strCompareFld and @strFieldToReturn.

 

[Connatic]

Aswell as doing what vongrunt says above, you could also use

execute sp_executesql, instead of just Exec

For this you will need to change @SQLCommand to NVarchar

sp_executesql Is a system Stored procedure and is a far better method of Executing Dynamic SQL statements!

Also have you tried using a Print statement in Query Analyser for @SQLCommand to make sure it says what you want it to say !!

 

[bertrandkis]

Hello there, after analysing your sp, I saw nothing wrong that will prevent it from runing,but you were not explicit enough in saying what error you had or what output you had when runing it. Just few word of advice, you don't need to qualify column names (Select tablename.columnname from tablename) when your query involves only 1 table. SQL server automatically maps the column name to the table name specified in the where clause. By not using the qualified name in your select list, you reduce potentiality of error. Also avoid unsing parenthesis when not needed, when assiging values to variable you can do a direct assignment like set @foo='hola if you hear me' Introducing brackets is also a potential for errors. Lastly let's eliminate everything that can affect our dynamic string.
1)Change the datatype of @strTableName to varchar(50)
2)Put this code in your stored procedure just before the EXEC @sqlcommand:

Print @sqlCommand
Return
Run your stored procedure from the Query Analyser, copy the output (the sql string printed) and paste it back in the query Analyser and run it.
If you do this You should get a good indication of where the error is. IF IT STILL DOES NOT WORK PLEASE TELL US EXACTLY THE PROBLEM( a syntax error? an incorrect output or ?????)
Cheers