How do I use database name dynamicaly in SQL query

Question

0.00/5 (No votes)

See more:

Hi,
Here i have 2 database one is my FARVISIONDB and another is JKUMARJV.Both have same table.Here use check organisation i.e database from front end.Here i have created stored procedure with datanm as parametere.

What I have tried:

So my query inside stored procedure like 

<pre>DECLARE @cols AS NVARCHAR(MAX),
        @query  AS NVARCHAR(MAX),@dbnm varchar(1000),@rslt varchar(max)

set @dbnm='farvisiondb'

set @cols = 'STUFF((SELECT distinct '','' + QUOTENAME(STATENAME) 
            from ((select CODE from '+@dbnm+'..M_STATEWISEGSTINACTAG)  e inner join
                 (select CODE,STATENAME from '+@dbnm+'..M_STATEMASTER)  f on e.code=f.code)  
                FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)'') ,1,1,'''')'

print @cols

EXEC sp_executesql @cols, N'@rslt varchar output',@rslt OUTPUT

It prints query perfectly i.e when i pass farvisiondb as parameter then it perfect

STUFF((SELECT distinct ',' + QUOTENAME(STATENAME) 
                    from (                   
                      (select CODE from farvisiondb..M_STATEWISEGSTINACTAG)  e inner join
                      (select CODE,STATENAME from farvisiondb..M_STATEMASTER)  f on e.code=f.code
                         )  
                    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')

But it not excecute.
Please help me.Thanks in advance.

Posted 12-Jul-17 19:58pm

SujataJK

Updated 14-Jul-17 5:04am

Add a Solution

Comments

BinnyVishwant 13-Jul-17 4:36am

Is this working for JKUMARJV db?

SujataJK 13-Jul-17 4:46am

no.I mean in 1 st query .I have print command so its results my second query which i shown above.That means my query executed up to print statement but when i am going to execute @cols then it shows error like

Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FOR'.

BinnyVishwant 13-Jul-17 5:08am

can you try to remove onedot(.)i,e. farvisiondb..M_STATEWISEGSTINACTAG >
farvisiondb.M_STATEWISEGSTINACTAG

BinnyVishwant 13-Jul-17 5:09am

Also try to execute only the select stmt first and check whether you are getting required resuts! by giving your databasename manually

SujataJK 13-Jul-17 5:18am

yes i had tried .Is working when i provide manual database name

BinnyVishwant 13-Jul-17 5:22am

try to give alias name before FOR

" )as a
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')"

SujataJK 13-Jul-17 5:27am

I also tried this it gives mi same error.
For further clearance Please check my stored procedure..

create procedure [dbo].[GSTIN_Update]
AS
BEGIN

DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(STATENAME)
from (
(select CODE from FARVISIONDB..M_STATEWISEGSTINACTAG) e inner join
(select CODE,STATENAME from FARVISIONDB..M_STATEMASTER) f on e.code=f.code
)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
print @cols;

set @query = 'SELECT CtrlAc, DESCR,' + @cols + ' from
(select c.CtrlAc,a.DESCR,s.GSTIN,stcd.statename from
(select accd,descr,MAINACCD from FARVISIONDB..M_LEDGER) as a inner join
(select code,Gstin,accd from FARVISIONDB..M_STATEWISEGSTINACTAG) as s on a.ACCD = s.ACCD inner join
(SELECT CODE,STATENAME,UT FROM FARVISIONDB..M_STATEMASTER) as STCD on s.CODE=stcd.CODE INNER JOIN
(SELECT ACCD,DESCR AS CtrlAc from FARVISIONDB..M_LEDGER) as C on c.ACCD=a.MAINACCD
) x
pivot
(
MAX(GSTIN)
for statename in (' + @cols + ')
) p '
execute(@query)
PRINT @query;
END;

This is MY static procedure i just want replace FARVISIONDB by @dbnm parametere.
here @Cols contains state name which i have used in @query.
i.e @Cols contains

[BIHAR],[DADRA AND NAGAR HAVELI],[DAMAN & DIU],[DELHI],[GOA],[GUJARAT],[HARYANA],[JHARKHAND],[KARNATAKA],[MADHYA PRADESH],[MAHARASHTRA],[RAJASTHAN],[TAMILNADU],[TELANGANA],[UTTAR PRADESH]

2 solutions

Solution 1

You need to fully qualify the table in the dynamic sql. That needs to be in the format [database].[schema].[table]. You are missing out the schema. E.g.

SQL

'FROM ' + @dbnm + '.dbo.M_STATEWISEGSTINACTAG) as STCD'

Posted 13-Jul-17 2:36am

CHill60

Updated 13-Jul-17 2:38am

v2

Comments

Richard Deeming 14-Jul-17 11:05am

At least in MS SQL Server, if you omit the schema, it will use the default schema for the current user.

It's still a good idea to include the schema; but that's not the cause of the OP's error. :)

SujataJK 17-Jul-17 1:00am

yes Richard u are correct

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)

Richard Deeming · Accepted Answer · 2017-07-14T05:04:00

Solution 2

You're missing the initial SELECT. You can also simplify the join to avoid the nested queries.

SQL

set @cols = N'SELECT STUFF((SELECT DISTINCT '','' + QUOTENAME(f.STATENAME) 
            FROM ' + QUOTENAME(@dbnm) + N'.dbo.M_STATEWISEGSTINACTAG As e INNER JOIN
                 ' + QUOTENAME(@dbnm) + N'.dbo.M_STATEMASTER As f ON e.code = f.code
            FOR XML PATH(''''), TYPE).value(''.'', ''NVARCHAR(MAX)''), 1, 1, '''')'

Posted 14-Jul-17 5:04am

Richard Deeming

Comments

SujataJK 17-Jul-17 1:05am

Yes when i did that
select @cols = STUFF((SELECT distinct ',' + QUOTENAME(STATENAME)
from (
(select CODE from FARVISIONDB..M_STATEWISEGSTINACTAG) e inner join
(select CODE,STATENAME from FARVISIONDB..M_STATEMASTER) f on e.code=f.code
)
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') ,1,1,'')
print @cols;
i am not able to write @dbnm(Variable) instead of FARVISIONDB .