Need returned value - 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 2000 Forums

SQL Server Development (2000)

Need returned value

Author

Topic

endsabruptly
Starting Member

12 Posts

Posted - 2004-12-06 : 09:52:01

I am needing to create this search criteria dynamically. I am also needing to retrieve the value returned by executing @SQLStatement inside of the stored procedure. Something like @Value = EXEC(@SQLStatement ) What am I missing?

--Build your search criteria
	SET @STATEMENTPARAMS =  "'"+'s-'+@ProdYear+'-'+@ProdMonth+'%'+"'"
--Build your search sting
	SET @SQLStatement = 'SELECT left(right(max(statementid),9),4) from oracle_batch where statementid LIKE '+@STATEMENTPARAMS
	EXEC(@SQLStatement)
GO

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-06 : 10:12:28

Use sp_executesql
see
http://www.nigelrivett.net/sp_executeSQL.html

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

endsabruptly
Starting Member

12 Posts

Posted - 2004-12-06 : 10:17:40

Unfortunatly, I'm not able to create another stored procedure. Is this the only way to accomplish this task?

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-06 : 10:53:50

Doesn't need to be an sp

declare @SQLStatement nvarchar(4000)
declare @s varchar(10)
SET @SQLStatement = 'SELECT @s = left(right(max(statementid),9),4) from oracle_batch where statementid LIKE '+@STATEMENTPARAMS
exec sp_executesql @SQLStatement, N'@s varchar(10) out), @s out

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

endsabruptly
Starting Member

12 Posts

Posted - 2004-12-06 : 11:23:25

Please forgive me, but I cannot get this code, or many revisions to work correctly.

	SET @SQLStatement ="SELECT @STATEMENTID1 = left(right(max(statementid),9),4) from oracle_batch where statementid LIKE "+@STATEMENTPARAMS
	exec sp_executesql @SQLStatement, N'@STATEMENTID1 varchar(100) output', @STATEMENTID1 output

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

nr
SQLTeam MVY

12543 Posts

Posted - 2004-12-06 : 11:46:22

@statement doesn't appear there so it can't be a problem with that code.
Maybe it is in @STATEMENTPARAMS or elsewhere.

If it was really @SQLStatement then you need to declare it as nvarchar not varchar (see my example).

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources