Query not executing dynamic way

Question

Post reply

Query not executing dynamic way

Sgar...

SSCarpal Tunnel

Points: 4409
More actions
May 26, 2016 at 5:20 am

#307998

1. Dynamic query
declare @dbname varchar(50),@objname varchar(20),@sql varchar(100)
set @dbname='XYZ'
Set @objname='TMP'
Set @sql=N'(Select * from '+@dbname+'.sys.objects where name = '+@objname+')'
EXEC @sql
2. Actual query
select * from XYZ.sys.objects where name='Tmp'
When I am executing query 2nd query I am getting results. But when I am trying with first way I ma getting error as :
Msg 911, Level 16, State 4, Line 5
Database '(Select * from XYZ' does not exist. Make sure that the name is entered correctly.
Can anyone will be able to help
Sagar Sonawane
** Every DBA has his day!!:cool:

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

joeroshan SSChampion Points: 10377 More actions · Answer 1

declare @dbname varchar(50),@objname varchar(20),@sql varchar(1000)

set @dbname='XYZ'

Set @objname='TMP'

Set @sql=N'Select * from '+@dbname+'.sys.objects where name = '''+@objname+''''

Execute (@sql)

----------------------------------------------------------------------------------------------------------------------------------------------------
Roshan Joe

Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

I MUCH prefer to do my dynamic SQL this way. Now I don't have to worry about if it is 1, 2, 3, N stupid single quotes to make the string correct!! 🙂

SET QUOTED_IDENTIFIER OFF

GO

declare @dbname varchar(50),@objname varchar(20),@sql varchar(1000)

set @dbname='XYZ'

Set @objname='TMP'

Set @sql="Select * from "+@dbname+".sys.objects where name = '"+@objname+"'"

Execute (@sql)

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

subramanian.esakkimuthu 40485 SSCrazy Points: 2022 More actions · Answer 3

declare @dbname varchar(50),@objname varchar(20),@sql varchar(100)

set @dbname='master'

Set @objname='Ta'

Set @sql="Select * from "+@dbname+".sys.objects where name = '"+@objname+"'"

EXEC (@sql)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

I prefer to use QUOTENAME which will escape quotes and brackets correctly, as well as handle weird names.

DECLARE

@dbname sysname,

@objname sysname,

@sql nvarchar(1000);

SET @dbname = 'XYZ';

SET @objname = 'TMP';

SET @sql = 'Select * from ' + QUOTENAME(@dbname) + '.sys.objects where name = ' + QUOTENAME( @objname, '''');

EXECUTE (@sql);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

Luis Cazares (6/2/2016)
I prefer to use QUOTENAME which will escape quotes and brackets correctly, as well as handle weird names.
DECLARE
@dbname sysname,
@objname sysname,
@sql nvarchar(1000);
SET @dbname = 'XYZ';
SET @objname = 'TMP';
SET @sql = 'Select * from ' + QUOTENAME(@dbname) + '.sys.objects where name = ' + QUOTENAME( @objname, '''');
EXECUTE (@sql);

That doesn't help in the case of filter values though. :ermm:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

TheSQLGuru (6/2/2016)
Luis Cazares (6/2/2016)
I prefer to use QUOTENAME which will escape quotes and brackets correctly, as well as handle weird names.
DECLARE
@dbname sysname,
@objname sysname,
@sql nvarchar(1000);
SET @dbname = 'XYZ';
SET @objname = 'TMP';
SET @sql = 'Select * from ' + QUOTENAME(@dbname) + '.sys.objects where name = ' + QUOTENAME( @objname, '''');
EXECUTE (@sql);
That doesn't help in the case of filter values though. :ermm:

No, that's why we use parametrized queries for filter values using sp_executesql.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 7

Luis Cazares (6/2/2016)
TheSQLGuru (6/2/2016)
Luis Cazares (6/2/2016)
I prefer to use QUOTENAME which will escape quotes and brackets correctly, as well as handle weird names.
DECLARE
@dbname sysname,
@objname sysname,
@sql nvarchar(1000);
SET @dbname = 'XYZ';
SET @objname = 'TMP';
SET @sql = 'Select * from ' + QUOTENAME(@dbname) + '.sys.objects where name = ' + QUOTENAME( @objname, '''');
EXECUTE (@sql);
That doesn't help in the case of filter values though. :ermm:
No, that's why we use parametrized queries for filter values using sp_executesql.

Sadly my experience has been that clients have a tough time or don't know proper use of sp_executesql and default to the easier direct execution. :crying:

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

Luis Cazares SSC Guru Points: 183698 More actions · Answer 8

TheSQLGuru (6/2/2016)
Luis Cazares (6/2/2016)
No, that's why we use parametrized queries for filter values using sp_executesql.
Sadly my experience has been that clients have a tough time or don't know proper use of sp_executesql and default to the easier direct execution. :crying:

I'm sure they'll find it easier to use once you show them some examples on how SQL Injection can hurt their databases.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2