[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 409
  • Last Modified:

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 !!!
0
nrajasekhar7
Asked:
nrajasekhar7
  • 2
  • 2
1 Solution
 
keyuCommented:
DEclare @servername as varchar(max)

@servername ='server1,server2,server3'

WHILE charindex(',',@servername )>0
    BEGIN
        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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what exactly is the problem here?
* adding a parameter to a stored procedure?
* using it?
* filling it with the proper value?
0
 
keyuCommented:
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
0
 
nrajasekhar7Author Commented:
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.
0
 
nrajasekhar7Author Commented:
Thanks !!!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now