Store procedure with Dynamic query

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi every,

I would like to do dynamic querying in a store procedure... I have a table which will store Table_Name and Field_Name With name tblConfig

Select * from tblConfig where id = @id

will give Table_Name(like tblUser) and Field_Name(like CID)

Now i want to select above Field_Name from above Table_Name and out put the result,
something like select (returned Field_Name) as fld_data from (returned Table_Name) in a store procedure.

So, Any help or an idea about this will be highly appreciated.

Thanks.

Posted 25-Nov-14 18:58pm

CringTee

Updated 25-Nov-14 18:59pm

v2

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sunil_Pawar · Accepted Answer · 2014-11-25T19:52:00

Try following SQL

SQL

DECLARE @DynamicQuery AS NVARCHAR(MAX)
DECLARE @ColNames AS NVARCHAR(MAX)
DECLARE @TblName AS NVARCHAR(MAX)
SELECT @ColNames= ISNULL(@ColNames + ',','')+ QUOTENAME(Field_Name) FROM (Select Field_Name from tblConfig Where id =1/*@id*/) as fld_data
SELECT @TblName = Table_Name FROM (Select Table_Name from tblConfig Where id =1/*@id*/) as tbl_data
Print @ColNames
Print @TblName
SET @DynamicQuery =  N' Select ' + @ColNames + '   from ' + @TblName + ' ';
EXEC sp_executesql @DynamicQuery

Jignesh Khant · Accepted Answer · 2014-11-25T20:35:00

Solution 3

SQL

declare @TableName nvarchar(50)
declare @FieldName nvarchar(50)
declare @Qry nvarchar(max)
set @TableName=(select Table_Name from tblConfig where id = @id)
set @FieldName=(select Field_Name from tblConfig where id = @id)

set @Qry=('select '+@FieldName ' from '+@TableName)
exec(@Qry)

Posted 25-Nov-14 20:35pm

Jignesh Khant

Shweta N Mishra · Accepted Answer · 2014-11-25T19:53:00

Solution 2

you may refer to this

SQL

Create Proc GetValues(@ID as int)
As
BEGIN
    Declare @SQL Nvarchar(max),@table nvarchar(30),@fieldname nvarchar(30)

select @table=table,@field name=fieldname from tblconfig where id=@ID

    set @SQL='select '+@field name+' from '+@table 

    exec Sp_Execute @SQL
END

Posted 25-Nov-14 19:53pm

Shweta N Mishra

Updated 25-Nov-14 21:53pm

v3

/\jmot · Accepted Answer · 2014-11-25T22:30:00

Use this links..

http://stackoverflow.com/questions/483798/creating-stored-procedure-in-another-database/483868#483868[^]
http://stackoverflow.com/questions/483798/creating-stored-procedure-in-another-database/793362#793362[^]
http://stackoverflow.com/questions/19875022/sql-server-dynamic-query-in-stored-procedure-in[^]
Building Dynamic SQL In a Stored Procedure[^]

Store procedure with Dynamic query

4 solutions

Solution 1

Solution 3

Solution 2

Solution 4

Add your solution here

Preview 0