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 :
So, can you please suggest me if there is any way to implement this in sql server 2008.
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
i need to do is to execute the queries in the count column....if there is any way to implement this.... Besides that you are creating stored procedure to select counts, but you can select that information in OUTER APPLY for example.. No need for stored procThis is a sample table.. in my real project the count column is similar with different queries based on the table). He wants TO EXECUTE QUERY. Also you are talking about performance, but implemented solution with stored procedures. Can you write the query that selects from tables and get desired data with your solution? In your solution at least scalar valued function would be better(not to mention OUTER APPLY)
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
