January 10, 2012 at 10:32 am
Hello,
Currently in the below stored procedure, I'm using a table variable to fetch results from the SELECT statement with GROUP BY.
Then the stored procedure code selects the count column (tabl_var_Count) from the same table variable basically to see if its greater than 0.
I have a user defined table type with below structure:
CREATE TYPE [dbo].UsrDefinedTblType AS TABLE
(uniqid_col [uniqueidentifier] NULL,
tbltype_id [int] NULL
)
GO
Sample Data:
uniqid_col tbltype_id
87C6E1FA-28CA-4D95-9CA9-E5A41AF8370C 1
03FB06E2-9563-4E13-A190-825551D55966 1
97FF5610-C092-4C6B-A913-00A764BE7260 2
Following is the stored procedure code:
USE [TESTDB]
GO
CREATE PROCEDURE [dbo].[test_sp]
@tbltype UsrDefinedTblType READONLY
AS
DECLARE @Count INT
DECLARE @tab_var TABLE
( tab_var_Id INT,
tabl_var_Count INT
);
BEGIN TRANSACTION
INSERT INTO @tab_var
SELECT tbltype_id, COUNT(tbltype_id)cnt
FROM @tbltype
WHERE tbltype_id IN (1,2,3,4,5)
GROUP BY tbltype_id
SELECT @Count = tabl_var_Count FROM @tab_var WHERE tab_var_Id = 1
IF @Count > 0
BEGIN
DELETE
FROM dbo.table_2
WHERE table_2_id IN (SELECT uniqid_col FROM @tbltype WHERE tbltype_id = 1)
END
COMMIT TRANSACTION
GO
Question 1:
I'm wondering if the select from the table variable "SELECT @Count = tabl_var_Count FROM @tab_var WHERE tab_var_Id = 1" could be avoided and instead something could be done in the below sql itself to assign the count of the below 5 rows into a variable. That way the select on table variable could be avoided.
The reason I'm asking is, in my original code, there are could of places in which the select from table variable is done ("SELECT @Count = tabl_var_Count FROM @tab_var WHERE tab_var_Id = 1").
SELECT tbltype_id, COUNT(tbltype_id)cnt
FROM @tbltype
WHERE tbltype_id IN (1,2,3,4,5)
GROUP BY tbltype_id
Question 2:[/u]
Is there a easy way to loop through the results of the select?
In the above example, how do I loop through the results of the below sql and assign it to a variable?
SELECT tbltype_id, COUNT(tbltype_id)cnt
FROM @tbltype
WHERE tbltype_id IN (1,2,3,4,5)
GROUP BY tbltype_id
In Oracle, I will be able to do a FOR on the SELECT and iterate through it and assign values to variable.
Example shown below.
Is something like this is possible in T-SQL?
FOR Cur_Count IN (SELECT tbltype_id, COUNT(tbltype_id) cnt
FROM @tbltype
WHERE tbltype_id IN (1,2,3,4,5)
GROUP BY tbltype_id
)
IF (Cur_Count.tbltype_id = 1) THEN
SET @Count_1 = Cur_Count.cnt
ELSE IF (Cur_Count.tbltype_id = 2) THEN
SET @Count_2 = Cur_Count.cnt
ELSE IF (Cur_Count.tbltype_id = 3) THEN
SET @Count_3 = Cur_Count.cnt
ELSE IF (Cur_Count.tbltype_id = 4) THEN
SET @Count_4 = Cur_Count.cnt
END IF
END LOOP;
Thanks!
January 10, 2012 at 11:24 am
Duplicated post :
http://www.sqlservercentral.com/Forums/Topic1232922-392-1.aspx
January 10, 2012 at 11:27 am
I knew you wouldn't have liked my reply...
-- Gianluca Sartori
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply