SQL SERVER 2008 Dynamic query problem

Posted by priyanka.sarkar on Stack Overflow See other posts from Stack Overflow or by priyanka.sarkar
Published on 2009-09-21T10:37:10Z Indexed on 2010/05/02 16:07 UTC
Read the original article Hit count: 264

Filed under:
|

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

© Stack Overflow or respective owner

Related posts about sql

Related posts about sql-server