How Do I Pass Database Name As Parameter Is Sql Stored Procedure?

Question

4.00/5 (2 votes)

See more:

Hello House;
I have have the following sql script:

SQL

SELECT pp.title, pp.surname,pp.firstname,pp.phone,pp.emai,rcd.reg_code,pp.genCode
    FROM SchoolAngelPortalDB.dbo.primaryparent pp
    LEFT JOIN Newschoolcore.dbo.registration_codes rcd ON pp.genCode = rcd.reg_code
    WHERE rcd.name = @schoolName 
)

how do i use

SchoolAngelPortalDB

and

Newschoolcore

as parameter and pass the parameter value in C#, so that i could accept the db name as input from user.

Thanks in advance.

Posted 2-Jun-14 1:43am

Uwakpeter

Updated 2-Jun-14 1:48am

v3

Add a Solution

Comments

Nirav Prabtani 2-Jun-14 7:52am

Can you elaborate it i can not understand what you exactly want?

Uwakpeter 2-Jun-14 8:41am

i want to be able to accept database name from user's input and pass it as parameter to the stored procedure parameter value.

4 solutions

Solution 1

Hi
Please check this url.

http://itknowledgeexchange.techtarget.com/itanswers/passing-a-database-name-as-a-stored-procedure-parameter/[^]

Regards
Dominic

Posted 2-Jun-14 1:57am

Dominic Abraham

Solution 4

Visit here....this will help you

how-to-switch-database-inside-stored-procedure[^]

Posted 2-Jun-14 2:24am

Rakesh Meel

Comments

Uwakpeter 3-Jun-14 8:06am

Hello Rakesh,

I am still having issues on this, please check my script, and let me know if i am missing anything:

set quoted_identifier off
go
ALTER procedure [dbo].[usp_accessdb]
@SchoolAngelPortalDB varchar(128),
@GSC_NewSchoolCore varchar(128),
@schoolName varchar(50)= NULL
as
declare @query varchar(1000)
set @query = 'SELECT pp.title, pp.surname,pp.firstname,pp.phone,pp.email,rcd.reg_code,pp.genCode
FROM '+@SchoolAngelPortalDB+'.dbo.primaryparent pp
LEFT JOIN '+ @GSC_NewSchoolCore +'.dbo.registration_codes rcd ON pp.genCode = rcd.reg_code
WHERE Sch.Name='+@schoolName

exec (@query)
go

When trying to execute the procedure,using this script:

[usp_accessdb]"SchoolAngelPortalDB","GSC_NewSchoolCore","Greenwood House School"
i am having this error:
Incorrect syntax near 'House'.

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)

TrushnaK · Accepted Answer · 2014-06-02T02:09:00

Solution 3

Refer this link first it is useful:-
http://forums.databasejournal.com/showthread.php?36588-Passing-database-name-to-a-stored-procedure[^]

you can try like this-

SQL

set quoted_identifier off
go
Create procedure usp_accessdb @dbname1 varchar(128),@dbname2 varchar(128),@schoolName varchar(100)
as

declare @query varchar(1000)
set @query = "SELECT pp.title, pp.surname,pp.firstname,pp.phone,pp.emai,rcd.reg_code,pp.genCode
    FROM @dbname1.dbo.primaryparent pp
    LEFT JOIN @dbname2.dbo.registration_codes rcd ON pp.genCode = rcd.reg_code
    WHERE rcd.name = @schoolName "
exec (@query)
go

Posted 2-Jun-14 2:09am

TrushnaK

Comments

Uwakpeter 2-Jun-14 8:54am

I tried running the script using:

USE [NewSchoolCore]
GO

DECLARE @return_value int

EXEC @return_value = [dbo].[usp_accessdb]
@dbname1 = N'NewSchoolCore',
@dbname2 = N'SchoolAngelPortalDB',
@schoolName = N'Greenwood House School'

SELECT 'Return Value' = @return_value

GO

It is reporting error: Must declare the scalar variable "@dbname1". Is there anything i have missed here?

Member 10812402 3-Jun-14 4:10am

declare declare @dbname1 varchar(1000) and declare @dbname2 varchar(1000). before use

Uwakpeter 3-Jun-14 8:04am

Hello Trusknak,

I am still having issues on this, please check my script, and let me know if i am missing anything:

set quoted_identifier off
go
ALTER procedure [dbo].[usp_accessdb]
@SchoolAngelPortalDB varchar(128),
@GSC_NewSchoolCore varchar(128),
@schoolName varchar(50)= NULL
as
declare @query varchar(1000)
set @query = 'SELECT pp.title, pp.surname,pp.firstname,pp.phone,pp.email,rcd.reg_code,pp.genCode
FROM '+@SchoolAngelPortalDB+'.dbo.primaryparent pp
LEFT JOIN '+ @GSC_NewSchoolCore +'.dbo.registration_codes rcd ON pp.genCode = rcd.reg_code
WHERE Sch.Name='+@schoolName

exec (@query)
go

When trying to execute the procedure,using this script:

[usp_accessdb]"SchoolAngelPortalDB","GSC_NewSchoolCore","Greenwood House School"
i am having this error:
Incorrect syntax near 'House'.

**Nirav Prabtani** · Accepted Answer · 2014-06-02T02:01:00

Solution 2

Use dynamic query in sql for that.. :)

e.g.

SQL

declare @query varchar(1000),@dbname varchar(50),@tableName varchar(50)
set @dbname='DatabaseName'
set @tableName='TableName'
set @query = 'select * from ' +@dbname+'.[dbo].'+@tableName
exec (@query)

Passing database name to a stored procedure[^]

Posted 2-Jun-14 2:01am

Nirav Prabtani

Updated 2-Jun-14 2:14am

v2

Comments

Uwakpeter 2-Jun-14 8:42am

The i do i pass the parameter value from user's input to this?

Nirav Prabtani 2-Jun-14 8:43am

yes you can

Uwakpeter 2-Jun-14 8:59am

please can you show how, i know how to pass parameter to normal field name in a table, but passing parameter to database, that i haven't done before, or is it going to be called the same way?

Uwakpeter 3-Jun-14 8:05am

Hello Nirav,

I am still having issues on this, please check my query, and let me know if i am missing anything:

set quoted_identifier off
go
ALTER procedure [dbo].[usp_accessdb]
@SchoolAngelPortalDB varchar(128),
@GSC_NewSchoolCore varchar(128),
@schoolName varchar(50)= NULL
as
declare @query varchar(1000)
set @query = 'SELECT pp.title, pp.surname,pp.firstname,pp.phone,pp.email,rcd.reg_code,pp.genCode
FROM '+@SchoolAngelPortalDB+'.dbo.primaryparent pp
LEFT JOIN '+ @GSC_NewSchoolCore +'.dbo.registration_codes rcd ON pp.genCode = rcd.reg_code
WHERE Sch.Name='+@schoolName

exec (@query)
go

When trying to execute the procedure,using this script:

[usp_accessdb]"SchoolAngelPortalDB","GSC_NewSchoolCore","Greenwood House School"
i am having this error:
Incorrect syntax near 'House'.