asked on

How to Pass the Variable in the Stored Procedure

Hi

Using the Ms-sql Server 2008 Database .
We have twenty Servers, one Reporting Servere.
Currently we have created 20 Insert Procedures to Insert the records in to Report Server.
Please Can you give me Better example to Pass the Varibale as Servername in the Stored Procedures.So that instead of using 20 Insert Procedures we can you use only one InsertProcedure.

Looking forward for the Best Solutions!!

Thanks !!!

ASKER CERTIFIED SOLUTION

keyu

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Guy Hengel [angelIII / a3]

what exactly is the problem here?
* adding a parameter to a stored procedure?
* using it?
* filling it with the proper value?

keyu

sorry one paratheses is missing in my last comment..
SET @servername = substring(@servername , charindex(',',@servername )+1, LEN(@servername ) - @pos)
so it will be now..

DEclare @servername as varchar(max)
declare @pos as int
DEclare @ServerID as varchar(100)

@servername ='server1,server2,server3'
SET @pos=0
WHILE charindex(',',@servername )>0
BEGIN
set @pos= charindex(',',@servername)
SET @ServerID = cast(substring(@servername ,0, charindex(',',@servername)) as int)
INSERT INTO [tblUserRoles] ([UserID], [serverid]) VALUES (@UserID, @ServerID)
SET @servername = substring(@servername , charindex(',',@servername )+1, LEN(@servername ) - @pos) --remove the first item from the list
END
for more info on this..refer below link..

http://www.vbforums.com/showthread.php?507592-SQL-Server-2005-Loop-through-split-a-delimited-string

nrajasekhar7

ASKER

Please any one Help with the Better Performance ,
As we have are having the Different servers with the same Tables (50) in all servers.
For insert using cursors for to fetch the records and to check.

Thanks in advance.

nrajasekhar7

ASKER

Thanks !!!