CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Nimko
Nimko

Reputation: 139

How to pass each value of the column from a table to a stored procedure and save the result in a table in SQL-Server

I have a table with username field as given below. I have a stored procedure which receives the username argument and process to show the results. At the moment I am manually executing the stored procedure for each username by specifying the argument and after execution copy paste its result in excel. The table with username is:

╔════╦══════════╦══╗
║ ID ║ Username ║  ║
╠════╬══════════╬══╣
║  1 ║ abc      ║  ║
║  2 ║ def      ║  ║
║  3 ║ ghi      ║  ║
║  4 ║ jkl      ║  ║
║  5 ║ mno      ║  ║
║  6 ║ xyz      ║  ║
╚════╩══════════╩══╝

The stored procedure is:

spCalculateSomeValuesForThePassedUsername 'abc'

Its output of the stored procedure is: 

╔════╦══════════╦═══════╗
║ ID ║ Username ║ Value ║
╠════╬══════════╬═══════╣
║  1 ║ abc      ║   100 ║
╚════╩══════════╩═══════╝

I can make a parent stored procedure which contains all the stored procedure and execute it to get the results but I will still have to copy paste the results. E.g.

CREATE PROCEDURE SPparent
AS
BEGIN
spCalculateSomeValuesForThePassedUsername 'abc'
spCalculateSomeValuesForThePassedUsername 'def'
spCalculateSomeValuesForThePassedUsername 'ghi'
spCalculateSomeValuesForThePassedUsername 'jkl'
.
.
.
END

I am wondering if it will be possible to get the username and paste the output to a result table automatically.

The desired Result table

╔════╦══════════╦═══════╗
║ ID ║ Username ║ Value ║
╠════╬══════════╬═══════╣
║  1 ║ abc      ║   100 ║
║  2 ║ def      ║   200 ║
║  3 ║ ghi      ║   150 ║
║  4 ║ jkl      ║   300 ║
║  5 ║ mno      ║   700 ║
║  6 ║ xyz      ║  1000 ║
╚════╩══════════╩═══════╝

Upvotes: 2

Views: 119

Answers (5)

Rachel Ambler
Rachel Ambler

Reputation: 1594

Declare @UserName NVarChar(128);

Create Table #Results(Id Int, UserName NVarChar(128), Value Int);

Declare curSO Cursor Local
    Fast_Forward
    Read_Only
        For
            Select UserName
              From mySchema.myTable
;

Open curSO;

Fetch Next From curSO
    Into @UserName;

While @@Fetch_Status = 0
Begin

    Insert Into #Results
    Exec spCalculateSomeValuesForThePassedUsername @UserName;

    Fetch Next From curSO
        Into @UserName;
End

Close curSO;

Deallocate curSO;

-- Or do something different here. I used a temporary table. You can use whatever
Select *
From #Results

Upvotes: 1

Vyacheslav Shcherbak
Vyacheslav Shcherbak

Reputation: 94

SET QUOTED_IDENTIFIER ON 
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE spCalculateSomeValuesForThePassedUsername AS RETURN
GO
ALTER PROCEDURE spCalculateSomeValuesForThePassedUsername (
    @Username varchar(255)
) 
AS
    SELECT @Username, 0
GO

CREATE PROCEDURE spParent AS RETURN
GO
ALTER PROCEDURE spParent AS
BEGIN
    SET NOCOUNT ON
    DECLARE @t AS table (id int IDENTITY(1,1), Username varchar(255), Value int)
    DECLARE @num int = 0
    DECLARE @Username varchar(255)
    DECLARE @ret int = 1

    -- DROP TABLE #Users
    CREATE TABLE #Users (
        id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
        Username varchar(255) NOT NULL
    )
    INSERT INTO #Users (Username)
    SELECT UserName FROM (VALUES ('abc'), ('def'), ('ghi')) AS t (UserName)

    WHILE @ret > 0
    BEGIN
        IF @UserName IS NOT NULL
            INSERT INTO @t (Username, Value)
            EXEC sp_executesql N'Exec spCalculateSomeValuesForThePassedUsername @Username;', '@Username varchar(255)', @Username = @Username;

        SELECT TOP(1) @Username = UserName, @num = id FROM #Users WHERE id > @num ORDER BY id ASC
        SET @ret = @@ROWCOUNT
    END
    SELECT * FROM @t
END
GO

EXEC spParent
GO

DROP PROCEDURE spParent
GO
DROP PROCEDURE spCalculateSomeValuesForThePassedUsername
GO

SET QUOTED_IDENTIFIER OFF 
SET ANSI_NULLS ON 
GO

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56735

And here's a much shorter set-oriented solution that uses no loops or cursors:

CREATE TABLE #Results(ID INT, Username SYSNAME, Value INT);
DECLARE @sql AS NVARCHAR(MAX) = N'';
SELECT @sql = @sql + N'
INSERT #Results EXEC spCalculateSomeValuesForThePassedUsername N'''+Username+''';'
FROM <yourTable>;
EXEC(@sql);

Select * from #Results;

Upvotes: 1

Krishnraj Rana
Krishnraj Rana

Reputation: 6656

Another solution is using while loop like this.

DECLARE @id INT, @UserName VARCHAR(100)

SELECT TOP (1) @id = id ,@UserName = UserName
FROM myTable;

WHILE @@rowcount > 0
BEGIN
    EXEC sp_executesql N'Exec spCalculateSomeValuesForThePassedUsername @UserName;'
        ,N'@UserName VARCHAR(100)'
        ,@UserName = @UserName;

    SELECT TOP (1) @id = id ,@UserName = UserName
    FROM myTable
    WHERE id > @id;
END;

Upvotes: 0

Abhishek
Abhishek

Reputation: 2490

One way of doing it would be something like this - 

CREATE PROCEDURE SPparent
AS
BEGIN
DECLARE @id INT, @UserName VARCHAR(100)
DECLARE @TempTable TABLE (id INT,UserName VARCHAR(100),Value INT)
DECLARE sample_cursor CURSOR FOR 
SELECT ID,UserName
FROM <yourtable>

OPEN sample_cursor
FETCH NEXT FROM sample_cursor 
INTO @id, @UserName

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @TempTable
    Exec spCalculateSomeValuesForThePassedUsername @UserName

FETCH NEXT FROM sample_cursor 
INTO @id, @UserName

END

CLOSE sample_cursor

SELECT id,UserName,Value FROM @TempTable

END

So here you loop through the initial records from your table and call the sp spCalculateSomeValuesForThePassedUsername for all the UserName and store them in a temp table. Once done you just select the temp table to get all the records in a table format.

Upvotes: 1

Related Questions