database server name as a parameter for a stored procedure

I want to create a stored proc that takes three paramaters (DBnamer name, ParentId and MNumberList) as in the attached code, but I keep getting error:

The DB server that I will use is added to the linked servers. The insert sql works if I use the actual values for servername.dbname, ParentID, and MNumber List instead of the variables.

Error Could not find server '@DbName' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers.

I am not sure if I am using any of the parameter variables correctly.

Please advise corrections.

Thanks.
CREATE PROCEDURE CDExtract_PopulateME 	
	 @DbName Varchar(50), 
	 @ParentID INT ,
	 @MNumberList Varchar(50)
AS
BEGIN
	INSERT into mySchema.CData_Extract_ME
(MemberId, ClientMemberId, Enroll_Start, Enroll_End, HICNum, MNumber, IPA, LPO, ParentID)
select  A.memberid as MemberID,
		B.memberid as ClientMemberID,
		B.Enroll_Start,
		B.Enroll_End,
		B.HICNum,
		B.MNumber,		
		NULL,
		NULL,
		@ParentID as ParentID  
from pd.dbo.member A (nolock)
join [@DbName].dbo.ME B on A.DisplayKey = B.hicnum
left join (select max(pcp_end_date) as pcp_end_date,memberid,network,subnetwork from pd.dbo.m_pcp group by memberid, network,subnetwork) C on A.memberid = C.memberid
where B.mnumber in (@MNumberList)
END
GO

Open in new window


The following insert SQL works, i need to create the above sproc to run this query with parameters.
INSERT into mySchema.CData_Extract_ME
(MemberId, ClientMemberId, Enroll_Start, Enroll_End, HICNum, MNumber,  IPA, LPO,ParentID)
select  A.memberid as MemberID,
		B.memberid as ClientMemberID,
		B.Enroll_Start,
		B.Enroll_End,
		B.HICNum,
		B.MNumber,		
		NULL,
		NULL,
		'211' as parentid 
pd.dbo.member A (nolock)
join [alph-clus-03\sql3].Excellus.dbo.ME B on A.DisplayKey = B.hicnum
left join (select max(pcp_end_date) as pcp_end_date,memberid,network,subnetwork from prod.dbo.m_pcp group by memberid, network,subnetwork) C on A.memberid = C.memberid
where B.mnumber in ('H1234','H5678')

Open in new window


patd1Asked:
Who is Participating?
 
wdosanjosConnect With a Mentor Commented:
You need to use dynamic sql. Something like this:

CREATE PROCEDURE CDExtract_PopulateME 	
	 @DbName Varchar(50), 
	 @ParentID INT ,
	 @MNumberList Varchar(50)
AS
BEGIN
        DECLARE @sql varchar(4000)
        SET @sql = '
	INSERT into mySchema.CData_Extract_ME
(MemberId, ClientMemberId, Enroll_Start, Enroll_End, HICNum, MNumber, IPA, LPO, ParentID)
select  A.memberid as MemberID,
		B.memberid as ClientMemberID,
		B.Enroll_Start,
		B.Enroll_End,
		B.HICNum,
		B.MNumber,		
		NULL,
		NULL,
		' + CAST(@ParentID AS VARCHAR(50)) + ' as ParentID  
from pd.dbo.member A (nolock)
join [' + @DbName + '].dbo.ME B on A.DisplayKey = B.hicnum
left join (select max(pcp_end_date) as pcp_end_date,memberid,network,subnetwork from pd.dbo.m_pcp group by memberid, network,subnetwork) C on A.memberid = C.memberid
where B.mnumber in (' + @MNumberList + ')'

EXEC (@sql)

END
GO

Open in new window


I hope this helps.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.