CODEWITHSUNDEEP
sqlsql-serversql-server-2008stored-procedures
Harsh
Harsh

Reputation: 3751

Running SQL query from a column in stored procedure

I have a table with a column containing queries to get count. I am writing a stored procedure to fetch the records from this table. What i need to do is to execute the queries in the count column and return the value in the same column. Below is a sample table with the count column.

So, when i run the stored procedure, i should get the result like this :

enter image description here

So, can you please suggest me if there is any way to implement this in sql server 2008.

Upvotes: 1

Views: 2491

Answers (2)

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35790

Despite the fact that this design is horrible, here is the way you can do what you need:

CREATE TABLE Users(ID int)
GO

CREATE TABLE Test(ID INT, Name NVARCHAR(20), Count NVARCHAR(200))
GO

INSERT  INTO Users
VALUES  ( 1 ),
        ( 1 ),
        ( 2 ),
        ( 3 )

INSERT  INTO Test
VALUES  ( 1, 'A', 'Select Count(*) From Users where ID = 1' ),
        ( 2, 'B', NULL ),
        ( 3, 'C', 'Select Count(*) From Users where ID = 3' ),
        ( 4, 'D', 'Select Count(*) From Users where ID = 1' )


DECLARE @Results TABLE
    (
      ID INT ,
      Name NVARCHAR(20) ,
      Count INT
    )
DECLARE @R TABLE ( ID INT )
DECLARE @ID INT ,
    @Name NVARCHAR(20) ,
    @Statement NVARCHAR(200) ,
    @Count INT

DECLARE cur CURSOR FAST_FORWARD READ_ONLY
FOR
    SELECT  ID , Name , Count FROM    Test

OPEN cur

FETCH NEXT FROM cur INTO @ID, @Name, @Statement

WHILE @@FETCH_STATUS = 0
    BEGIN

        DELETE  FROM @R

        INSERT  INTO @R
                EXEC ( @Statement )

        INSERT  INTO @Results
        VALUES  ( @ID, @Name, ( SELECT  * FROM    @R ) )  

        FETCH NEXT FROM cur INTO @ID, @Name, @Statement

    END

CLOSE cur
DEALLOCATE cur

SELECT  * FROM    @Results

Output:

ID  Name  Count
1   A     2
2   B     NULL
3   C     1
4   D     2

Upvotes: 1

Maciej Los
Maciej Los

Reputation: 8591

I agree with Sorrel Vesper. I do not understand why do you want to store query in a table.

Instead of this use/create proper SP:

CREATE PROCEDURE GetUserCount
    @userId INT
AS
BEGIN

    SET NOCOUNT ON;
    SELECT COUNT(*) AS UserCount
    FROM TableName
    WHERE UserId = @userId

END

For further information, please see: CREATE PROCEDURE

In case you want to fetch data for every single user, use query like this:

SELECT UserId, COUNT(*)
FROM TableName
GROUP BY UserId

That's all!

For further information, please see: Aggregate functions

Upvotes: 1

Related Questions