Enter dynamic database name while executing procedure in sql server

Question

I want to execute a procedure from another procedure for that I want to enter database name dynamically.

            SELECT @dname=dbname FROM @db WHERE id=@intFlag
            PRINT @dname
            EXEC [@dname].[dbo].[IPC_GetCount] @UserLogin,@Role,@Email,@Days,@Code,@Status

But it is not accepting the variable even though I am getting db name inside the variable.

Please help

See learn.microsoft.com/en-us/sql/relational-databases/… - you just build a string containing your SQL and pass it to that procedure. — ADyson
– ADyson, Commented Dec 19, 2018 at 10:40

Anson Aricatt · Accepted Answer · 2018-12-19 11:19:45Z

1

Can you try this query

SELECT @dname=dbname FROM   @db WHERE  id=@intFlag

PRINT @dname

DECLARE @query varchar(100) = 'EXEC ['+ @dname +'].[dbo].[IPC_GetCount] '+ @UserLogin +',' + @Role+',' + @Email +',' + @Days+',' +@Code +',' +@Status

EXECUTE (@query)

edited Dec 19, 2018 at 11:19

answered Dec 19, 2018 at 10:57

Anson Aricatt

3932 silver badges8 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rahul Aggarwal Over a year ago

giving error invalid column name "Invalid column name '@dname'."

Anson Aricatt Over a year ago

can you try now by changing [@dbname] to @dbname

Anson Aricatt Over a year ago

can you print @query variable and include in comment? Try with query execute in your database see results

Anson Aricatt Over a year ago

Then some of your variables are NULL please verify your variables are not NULL

Collectives™ on Stack Overflow

Enter dynamic database name while executing procedure in sql server

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related