CODEWITHSUNDEEP
sql-server-2008stored-procedures
NoviceMe
NoviceMe

Reputation: 3256

How to execute stored procedure multiple times to insert values in the table

I have following stored procedure that i want to execute in sql server 2008

USE [Students]
GO

DECLARE @return_value int

EXEC    @return_value = [School].[Student_Entry]
        @StudentName = N'Kenny',
        @StudentClass = N'1'

SELECT  'Return Value' = @return_value

GO

Running above stored procedure enters values in sql server table called Student. But i have like 100 values like this is there an easier way to insert multiple values together? or i will have to write above execute statement each time for each value?

Upvotes: 2

Views: 5109

Answers (1)

RBarryYoung
RBarryYoung

Reputation: 56745

Well assuming that you're using SSMS, then you can do this old school: Make the EXEC command into a single line, replace the arguments with "#", cut and paste that line 100 times. Then replace the "#"'s with the actual arguments from your list and execute.

Here's an example with 10 lines to get you started:

USE [Students]
GO

DECLARE @rt int

EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt
EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt
EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt
EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt
EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt
EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt
EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt
EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt
EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt
EXEC @rt = [School].[Student_Entry] @StudentName=N'#', @StudentClass=N'#'; SELECT 'Return Value'=@rt

GO

Upvotes: 1

Related Questions