January 9, 2012 at 11:15 pm
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 2:02 am
anonymous2009 (1/9/2012)
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.
There's no need to assign variables on a row basis, just delete the rows you don't want. Based on your code, the whole procedure could consist of just one statement:
DELETE
FROM dbo.table_2
WHERE table_2_id IN (
SELECT uniqid_col
FROM @tbltype
WHERE tbltype_id = 1
)
I suspect you're doing many other things in your actual code and you will find my answer unsatisfactory. If you could post the actual code (or part of it) we could try to help with it.
Question 2:
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)cntFROM @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.
You can achieve the same in SQLServer too using a cursor, but I won't show you how to do it unless you prove that you absolutely need it, which I doubt. Generally speaking, cursors are slow, consume a lot of memory and introduce unneeded locks. There are so little problems that have to be solved with cursors and can't be solved with declarative code that I highly doubt that you need one.
If you could post something closer to your actual code, I'm sure that lots of people here would help you turning it to high-performing declarative code.
Hope this helps
Gianluca
-- Gianluca Sartori
January 10, 2012 at 2:44 am
I've tried to work out what you are doing from the information provided. Please try the following example, based on your code, and let me know if I have misunderstood anything:
-- User defined table type
CREATE TYPE dbo.UsrDefinedTblType AS TABLE
(
uniqid_col uniqueidentifier PRIMARY KEY NONCLUSTERED,
tbltype_id integer NOT NULL
)
GO
-- Example table to delete from
CREATE TABLE dbo.table_2
(
table_2_id uniqueidentifier PRIMARY KEY NONCLUSTERED
)
GO
-- Sample data for the table to delete from
INSERT dbo.table_2
(table_2_id)
VALUES
({guid '87C6E1FA-28CA-4D95-9CA9-E5A41AF8370C'}),
({guid '03FB06E2-9563-4E13-A190-825551D55966'}),
({guid 'DB2A53C7-A9D2-4B8D-80DA-8A9332BC264D'})
GO
-- Procedure
CREATE PROCEDURE dbo.test_sp
@tbltype UsrDefinedTblType READONLY
AS
BEGIN
DELETE dbo.table_2
WHERE
table_2_id IN
(
SELECT
tt.uniqid_col
FROM @tbltype AS tt
WHERE
tt.tbltype_id = 1
)
END
GO
-- Show contents of table
SELECT * FROM dbo.table_2 AS t
-- Sample table parameter contents
DECLARE @tbltype UsrDefinedTblType;
INSERT @tbltype
(uniqid_col, tbltype_id)
VALUES
({guid '87C6E1FA-28CA-4D95-9CA9-E5A41AF8370C'}, 1), -- Match
({guid '03FB06E2-9563-4E13-A190-825551D55966'}, 1), -- Match
({guid '97FF5610-C092-4C6B-A913-00A764BE7260'}, 1) -- No match
-- Execute the procedure
EXECUTE dbo.test_sp @tbltype
-- Show contents of table after delete
SELECT * FROM dbo.table_2 AS t
GO
-- Tidy up
DROP PROCEDURE dbo.test_sp
DROP TABLE dbo.table_2
DROP TYPE UsrDefinedTblType
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 6:56 am
Anonymous, you have been given GREAT ADVICE by two of the best here and it goes to a FUNDAMENTAL DIFFERENCE between Oracle and SQL Server. To be effective/efficient with the latter you simply MUST stop "iterating" one-row-at-a-time in your design and code.
Oh, and please beware how inefficient GUIDs can be in MANY scenarios. 🙂
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply