Dynamic Stored procedure - 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

Dynamic Stored procedure

Author

Topic

fredand
Starting Member

4 Posts

Posted - 2012-01-09 : 01:30:36

Hi there all, been a long time reader, first question tho.

Is it possible to make a sp where i submit the name of the column as parameter and get the value of that column as result?

Maybe this code can show what i'm aiming for? (its the returning of the selected _value_ thats failing.


USE [companydatabase]
GO
/****** Object:  StoredProcedure [dbo].[Sp_company_updateUserDb]    Script Date: 01/09/2012 07:16:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[Sp_company_updateUserDb]
	@customerid varchar(30),
	@fieldname varchar(30)
AS
BEGIN

	SET NOCOUNT ON;
  declare @sqlquery varchar(100)
    
    -- Insert statements for procedure here
	set @sqlquery = 'SELECT ' + @fieldname + ' from usertable where customerid=' + @customerid 	
	
	execute(@sqlquery)

	print @fieldname	
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 01:39:16

Didnt understand what question is? whats the problem with above sp? also make sure you do enough checks to avoid possibilities of SQL Injection attack.

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

fredand
Starting Member

4 Posts

Posted - 2012-01-09 : 01:51:05

The sp wouldnt be accessible via web of anything just executed on the server.

In the return part i get the value the submitted parameter not the actual value in the table.

quote:
Originally posted by visakh16

Didnt understand what question is? whats the problem with above sp? also make sure you do enough checks to avoid possibilities of SQL Injection attack.

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-01-09 : 02:19:44

Did you get any error when trying that code?

Madhivanan

Failing to plan is Planning to fail

fredand
Starting Member

4 Posts

Posted - 2012-01-09 : 02:21:34

No, it prints out the submitted value of the parameter and not whats in the table :/

quote:
Originally posted by madhivanan

Did you get any error when trying that code?

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 02:34:08

thats because you just have print @fieldname

if you want value instead. you need to declare an output parameter and use sp_executesql to pass it to query and get the field value back
see second code snippet in link below

http://msdn.microsoft.com/en-us/library/ms188001.aspx

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-01-09 : 02:38:03

You have the following code

execute(@sqlquery)

So it should return the result from select statement

Madhivanan

Failing to plan is Planning to fail

fredand
Starting Member

4 Posts

Posted - 2012-01-09 : 02:51:42

I think this is it... i will try this... thanks to all for taking your time!

quote:
Originally posted by visakh16

thats because you just have print @fieldname

if you want value instead. you need to declare an output parameter and use sp_executesql to pass it to query and get the field value back
see second code snippet in link below

http://msdn.microsoft.com/en-us/library/ms188001.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 03:28:41

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources