stored procedure out paramter not working

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

Transact-SQL (2005)

stored procedure out paramter not working

Author

Topic

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-19 : 05:10:16

Hi,
iam trying to outparm in stored procedure but its not working,
iam daynamically passing fields and table name and i want to get
the keyid from that

error:
Msg 156, Level 15, State 1, Procedure getkeyid, Line 16
Incorrect syntax near the keyword 'exec'.

USE [smartsystem]
GO
/****** Object: StoredProcedure [dbo].[getkeyid] Script Date: 11/19/2010 15:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[getkeyid]
(
@fldgaugeid varchar(70),
@tablename varchar(50),
@keyfield varchar(50),
@descvalue varchar(75),
@getid int output
)
as
set nocount on

declare @sql nvarchar(max)

select @sql =
'select ' + @keyfield + ' from ' + @tablename + ' where ' + '''' +@descvalue+ '''' + ' = ' + @fldgaugeid
SELECT @getid= exec(@sql)

Desikankannan

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-19 : 05:12:10

have a look at sp_executesql

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-19 : 05:15:43

Also read this
www.sommarskog.se/dynamic_sql.html

Madhivanan

Failing to plan is Planning to fail

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 05:23:56

probably
select @sql =
'select @getid = ' + @keyfield + ' from ' + @tablename + ' where ' + '''' +@descvalue+ '''' + ' = ' + @fldgaugeid
exec sp_executesql @sql, N'@getid int out', @getid out

see
http://www.nigelrivett.net/SQLTsql/sp_executeSQL.html

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

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-19 : 05:31:42

quote:
Originally posted by pk_bohra

have a look at sp_executesql

below line is not working,

SELECT @getid= exec(@sql)

Desikankannan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 05:40:45

That's because you can't pipe the result of dynamic sql into a variable - you will only get the return code of the execution.
You could create a table variable an
insert @tbl
exec(@sql)

select @get_id = get_id from @tbl

have a look at the link I gave for sp_executesql or look in bol.
sp_executesql allows you to pass and receive parameter values into dynamic sql.
The code I gave should be pretty close.

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

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-19 : 05:52:17

quote:
Originally posted by desikankannan

quote:
Originally posted by pk_bohra

have a look at sp_executesql

below line is not working,

SELECT @getid= exec(@sql)

Desikankannan

iam not getting this line can u explain

exec sp_executesql @sql, N'@getid int out', @getid out

Desikankannan

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-19 : 06:03:43

quote:
Originally posted by nigelrivett

That's because you can't pipe the result of dynamic sql into a variable - you will only get the return code of the execution.
You could create a table variable an
insert @tbl
exec(@sql)

select @get_id = get_id from @tbl

have a look at the link I gave for sp_executesql or look in bol.
sp_executesql allows you to pass and receive parameter values into dynamic sql.
The code I gave should be pretty close.

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

Hi,
i tried ur code but it throw error

Msg 1087, Level 15, State 2, Procedure getkeyid, Line 18
Must declare the table variable "@tbl".

USE [smartsystem]
GO
/****** Object: StoredProcedure [dbo].[getkeyid] Script Date: 11/19/2010 15:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[getkeyid]
(
@fldgaugeid varchar(70),
@tablename varchar(50),
@keyfield varchar(50),
@descvalue varchar(75),
@getid int output
)
as
set nocount on

declare @sql nvarchar(max),
@tbl nvarchar(200)

select @sql =
'select @getid = ' + @keyfield + ' from ' + @tablename + ' where ' + '''' +@descvalue+ '''' + ' = ' + @fldgaugeid

insert @tbl exec(@sql)

Desikankannan

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 06:09:11

declare @sql nvarchar(max)
declare @tbl table (get_id int)

select @sql =
'select getid = ' + @keyfield + ' from ' + @tablename + ' where ' + '''' +@descvalue+ '''' + ' = ' + @fldgaugeid

insert @tbl exec(@sql)
selet @get_id = get_id from @tbl

sp_executesql is easier to return a single value.

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

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-19 : 06:34:33

quote:
Originally posted by nigelrivett

insert @tbl exec(@sql)
selet @get_id = get_id from @tbl

I feel that this is not supported.

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-19 : 06:36:34

quote:
Originally posted by nigelrivett

declare @sql nvarchar(max)
declare @tbl table (get_id int)

select @sql =
'select getid = ' + @keyfield + ' from ' + @tablename + ' where ' + '''' +@descvalue+ '''' + ' = ' + @fldgaugeid

insert @tbl exec(@sql)
selet @get_id = get_id from @tbl

sp_executesql is easier to return a single value.

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

hi,
i tried ur code it throws error to me
error
Conversion failed when converting the varchar value 'fldrefmstcode' to data type int.

USE [smartsystem]
GO
/****** Object: StoredProcedure [dbo].[getkeyid] Script Date: 11/19/2010 15:29:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[getkeyid]
(
@fldgaugeid varchar(70),
@tablename varchar(50),
@keyfield varchar(50),
@descvalue varchar(75)

)
as
set nocount on

declare @sql nvarchar(max),@gets_id int
declare @tbl table (get_id int)

select @sql =
'select getid = ' + @keyfield + ' from ' + @tablename + ' where ' + '''' +@descvalue+ '''' + ' = ' + @fldgaugeid

insert @tbl exec(@sql)
select @gets_id = get_id from @tbl

print @gets_id

exec getkeyid '3048','Std_mstmasterref','fldmstrefid','fldrefmstcode'

Desikankannan

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-19 : 06:39:53

An example for your understanding

declare @sql nvarchar(4000)
declare @tbl table (get_id int)
declare @tablename nvarchar(4000)
declare @descvalue nvarchar(4000)
declare @fldgaugeid nvarchar(4000)
declare @getid int

set @tablename ='sysobjects'
set @descvalue = 'id '
set @fldgaugeid = '6'

select @sql =
'select getid = count(*) from ' + @tablename + ' where ' + '' +@descvalue+ '' + ' = ' + @fldgaugeid

exec sp_executesql @sql, N'@getid int output', @getid output

Print @getid

I suggest that you understand the example as it will be helpful for you in future.

--Edit:
Try:

select @sql =
'select getid = ' + @keyfield + ' from ' + @tablename + ' where ' + '' +@descvalue+ '' + ' = ' + @fldgaugeid

exec sp_executesql @sql, N'@getid int output', @getid output

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-19 : 06:42:44

Can you post @sql that is executed please.
I'm guessing that either @keyfield is not an integer column or that @fldgaugeid is an integer and @descvalue contains 'fldrefmstcode'.

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

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-11-19 : 06:46:22

quote:
Originally posted by nigelrivett

Can you post @sql that is executed please.
I'm guessing that either @keyfield is not an integer column or that @fldgaugeid is an integer and @descvalue contains 'fldrefmstcode'.

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

One of the reason that OP is getting error because of
'''' +@descvalue+ '''' . This results in string and column name in where condition should not be in single quotes.

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-19 : 07:06:44

quote:
Originally posted by nigelrivett

Can you post @sql that is executed please.
I'm guessing that either @keyfield is not an integer column or that @fldgaugeid is an integer and @descvalue contains 'fldrefmstcode'.

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

iam passing parameter like this

exec getkeyid '3048','Std_mstmasterref','fldmstrefid','fldrefmstcode'

i getting error
Conversion failed when converting the varchar value 'fldrefmstcode' to data type int.

select @sql =
'select getid = ' + @keyfield + ' from ' + @tablename + ' where ' + '''' +@descvalue+ '''' + ' = ' + @fldgaugeid

Desikankannan

desikankannan
Posting Yak Master

152 Posts

Posted - 2010-11-19 : 07:16:24

quote:
Originally posted by nigelrivett

Can you post @sql that is executed please.
I'm guessing that either @keyfield is not an integer column or that @fldgaugeid is an integer and @descvalue contains 'fldrefmstcode'.

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

thanks working well

Desikankannan

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-19 : 07:48:54

quote:
Originally posted by pk_bohra

quote:
Originally posted by nigelrivett

insert @tbl exec(@sql)
selet @get_id = get_id from @tbl

I feel that this is not supported.

It is supported

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources