CODEWITHSUNDEEP
sqlsql-server
priyanka.sarkar
priyanka.sarkar

Reputation: 26498

SQL SERVER 2008 Dynamic query problem

I have a dynamic query which reads like this

Alter PROCEDURE dbo.mySP 
    -- Add the parameters for the stored procedure here
    (
        @DBName varchar(50),
        @tblName varchar(50)

    )

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
declare @string as varchar(50)
declare @string1 as varchar(50)

set @string1  = '[' + @DBName + ']' + '.[dbo].' + '[' + @tblName + ']'

set @string = 'select * from ' + @string1   

exec @string

END

I am calling like this

dbo.mySP 'dbtest1','tblTest'

And I am experiencing an error

"Msg 203, Level 16, State 2, Procedure mySP, Line 27
The name 'select * from [dbtest1].[dbo].[tblTest]' is not a valid identifier."

What is wrong? and How to overcome?

Thanks in advance

Upvotes: 0

Views: 4909

Answers (4)

Manjini.K
Manjini.K

Reputation: 9

ALTER PROCEDURE test_sp
    -- Add the parameters for the stored procedure here
    (
        @DBName varchar(50),
        @tblName varchar(50)

    )

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON

-- Insert statements for procedure here
declare @string as varchar(100)
declare @string1 as varchar(50)

set @string1  = '[' + @DBName + ']' + '.[dbo].' + '[' + @tblName + ']'
Print @string1
set @string = 'select * from'  + @string1       
Print @string
exec (@string)
SET NOCOUNT OFF  
END

Upvotes: 0

Colin Mackay
Colin Mackay

Reputation: 19175

It thinks that the contents of @string refer to a stored procedure name. You need to put

EXEC (@string)

or better use the stored procedure sp_executesql

You should also set up some guard code to check that the values you are passing in are the names of real tables and databases. You can query the views in the INFORMATION_SCHEMA to validate the input.

You can read more on safer dynamic SQL on my blog.

Upvotes: 5

KM.
KM.

Reputation: 103587

if you use EXEC as:

EXEC @String

it is trying to run a procedure with the name contained within the @String variable. try it out:

create procedure TestProc
as
print 'you called TestProc!'
go

declare @string varchar(20)
set @string='TestProc'

exec @string

if you use EXEC as:

EXEC (@Query)

you run the sql within the @Query variable, try it out:

DECLARE @Query  varchar(50)
set @Query='Print ''just ran it!'''

EXEC (@Query)

Upvotes: 1

Druid
Druid

Reputation: 6453

Change

exec @string

To

exec(@string)

Here's a working SP I just tested:

CREATE PROCEDURE [dbo].[test] 
    @DBName varchar(50),
    @tblName varchar(50)
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @string AS VARCHAR(50)
    DECLARE @string1 AS VARCHAR(50)

    SET @string1 = '[' + @DBName + '].[dbo].[' + @tblName + ']'
    SET @string = 'select * from ' + @string1

    EXEC(@string)
END

Upvotes: 4

Related Questions