return variable problem - 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

General SQL Server Forums

New to SQL Server Programming

return variable problem

Author

Topic

joseph17
Starting Member

5 Posts

Posted - 2012-07-17 : 10:56:27

Hi everyone,
I have a simple stored procedure that I would like to have return a int (my primary key for that row) based upon two variables I pass into it from my program. I can return my desired output if I code in one of the values but get nothing returned if I use a variable as the value. I need some help. Here's what I have for my stored procedure:

ALTER PROCEDURE [dbo].[sp_idFromProductTable]

@refColumn varchar(10),
@refId int
AS
BEGIN

SET NOCOUNT ON;

declare @partId int

set @partId = (select product_id FROM tbl_product WHERE @refColumn + '_id%' like @refId)

return @partId
END

If I dont declare a local variable and only pass in a value for @refId such as this:

select product_id FROM tbl_product WHERE motor_id like @refId

I get my desired results. Any help would be greatly appreciated. Thank you all.

Joseph

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 11:00:18

you need dynamic sql for this. use sp_executesql

see

http://support.microsoft.com/kb/262499

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-07-17 : 15:01:35

hold on

X002548
Not Just a Number

15586 Posts

Posted - 2012-07-17 : 15:11:09

[code]

CREATE PROCEDURE [dbo].[sp_idFromProductTable]
@refColumn varchar(10)
, @refId int
, @partId int OUTPUT
AS
BEGIN
SET NOCOUNT ON;

/*

-- Create this for all processes

CREATE TABLE myTemp99(Spid int, Product_id int)
*/

DECLARE @sql varchar(8000)
SET @sql = 'INSERT INTO myTemp99(Spid, Product_id) SELECT @@SPID, Product_id FROM tbl_product '
+ ' WHERE ' + @refColumn + '+id LIKE ' + '''' + refId + '%' + '''' + ';'
DELETE FROM myTemp99 WHERE Spid = @@SPID
EXEC(@sql)
SELECT PartId = Product_id myTemp99 WHERE Spid = @@SPID
RETURN; -- Never supply a value to RETURN, SQL Server CAN Override your value in certain circumstances
END

[/code]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 15:21:30

quote:

Originally posted by X002548



CREATE PROCEDURE [dbo].[sp_idFromProductTable] 
  @refColumn	varchar(10)
, @refId		int
, @partId		int OUTPUT
AS
  BEGIN
	SET NOCOUNT ON;

/*

	-- Create this for all processes
	 
	CREATE TABLE myTemp99(Spid int, Product_id int)
*/

	DECLARE @sql varchar(8000)
	SET @sql = 'INSERT INTO myTemp99(Spid, Product_id) SELECT @@SPID, Product_id FROM tbl_product '
			 + ' WHERE ' + @refColumn + '+id LIKE ' + '''' + refId + '%' + '''' + ';'
	DELETE FROM myTemp99 WHERE Spid = @@SPID
	EXEC(@sql)
	SELECT PartId = Product_id  myTemp99 WHERE Spid = @@SPID
	RETURN;	-- Never supply a value to RETURN, SQL Server CAN Override your value in certain circumstances
  END

one case is when variable supplied has NULL value. is there any other case where value gets overidden by SQL?

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 15:22:05

Also using RETURN you can return only integer type values

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

X002548
Not Just a Number

15586 Posts

Posted - 2012-07-17 : 15:28:15

SQL Server, depending on the severity of an error, will override the value in RETURn

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-17 : 22:51:15

quote:
Originally posted by X002548

SQL Server, depending on the severity of an error, will override the value in RETURn

ok. thats when error returns

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

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-18 : 12:16:48

That's kind of a misnomer. When there are certian severities (20 or greater) that termiante the database connection, so you are already in a really bad state at that point. At anyrate, a return code is rather pointless these days as, with SQL 2012, we have more structured error handling.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources