Select Statement with T-SQL Control Statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Statement with T-SQL Control Statement

Author

Topic

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-08 : 19:30:19

ok I havent done this before and I've been reading to no prevail. I know the syntax is off but cutting and pasting this code should be self explanatory, I wanna run a different select based on the value passed to the SP

Thanks in advance...

CREATE PROCEDURE sp_select500
(
@genderID tinyint
)
AS

CASE @genderID

WHEN '2' THEN
BEGIN
SELECT TOP 500 nameOnline, rating, votes, userID, date FROM tblUserDetails WHERE genderID = 2 AND active = 1 ORDER BY DATE DESC
END

WHEN '1' THEN
BEGIN
SELECT TOP 500 nameOnline, rating, votes, userID, date FROM tblUserDetails WHERE genderID = 1 AND active = 1 ORDER BY DATE DESC
END

WHEN '3' THEN
BEGIN
SELECT TOP 500 nameOnline, rating, votes, userID, date FROM tblUserDetails WHERE active = 1 ORDER BY DATE DESC
END
END

GO

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-08 : 19:38:22

You might find this simpler to just create one SELECT statement and use a little Dynamic SQL to build it appropriately. Check these articles:

[url]http://www.sqlteam.com/item.asp?ItemID=4599[/url]
[url]http://www.sqlteam.com/item.asp?ItemID=4619[/url]

----------------------------------------------------------------------
Only 1 more! Almost to the summit! Oh, look at the time... See ya!

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

byrmol
Shed Building SQL Farmer

1591 Posts

Posted - 2002-01-08 : 19:40:57

Mike,

The CASE keyword must be part used in a context where the value resulting from the case expression is in another expression.

eg.


Select case when 1 then 2 else 3 end from blah

not


case when 1 then 2 else 3 end

As you can see the last example has no context and is thus meaningless..

What you are after is the IF keyword....


CREATE PROCEDURE sp_select500
(
@genderID tinyint
)
AS

IF @genderID > 2
  SELECT TOP 500 nameOnline, rating, votes, userID, date FROM tblUserDetails WHERE active = 1 ORDER BY DATE DESC
ELSE
  SELECT TOP 500 nameOnline, rating, votes, userID, date FROM tblUserDetails WHERE genderID = @genderID  AND active = 1 ORDER BY DATE DESC
go

DavidM

Tomorrow is the same day as Today was the day before.

Edited by - byrmol on 01/08/2002 19:42:56

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-09 : 03:30:58

thanks for the timely response it worked like a charm .. EXCEPT i realized I need something slightly different so I had to add a few changes .... Unfortunately I get this error:

Server: Msg 8101, Level 16, State 1, Procedure sp_GetRandomRecord_RateBodies, Line 29
An explicit value for the identity column in table '#TempRandomUser' can only be specified when a column list is used and IDENTITY_INSERT is ON.

Here's the modded SP... Thanks a bunch for any help received.. this stuff is over my head thank god I dont have to do too much of it :)

CREATE PROCEDURE sp_GetRandomRecord_RateBodies
(
@genderID tinyint
)
AS

--declare @nRecordCount int
--declare @nRandNum int

-- Create a temporary table with the same structure of , rating, votes, userID
-- the table we want to select a random record from

CREATE TABLE #TempRandomUser

(
nameOnline varchar(15),
votes float(8),
points float(8),
userID int,
idNum int identity(1,1)
)

-- Dump the contents of the table to seach into the
-- temp. table
--INSERT INTO #RandomUser
set identity_insert #tempRandomUser on
IF @genderID > 2

INSERT INTO #TempRandomUser SELECT TOP 500 nameOnline, votes, points, userID FROM tblUserDetails WHERE active = 1 ORDER BY DATE DESC

ELSE

INSERT INTO #TempRandomUser SELECT TOP 500 nameOnline, votes, points, userID FROM tblUserDetails WHERE genderID = @genderID AND active = 1 ORDER BY DATE DESC
go

-- Select a random number between 1 and the number
-- of records in our table
declare @nRandNum int
Select @nRandNum = Round(((498) * Rand() + 1), 0)

-- Select the record from the temp table with the
-- ID equal to the random number selected...
Select nameOnline, votes, points, userID From #TempRandomUser
Where idNum = @nRandNum
Go

Nazim
A custom title

1408 Posts

Posted - 2002-01-09 : 03:49:22

Firstly, Rename your Stored Procedure , NEVER start a Stored procedure with sp_ they are explicitly used to signify system stored procedure and when you name it that way there will be slight performace decrease as it first searches the Master DB and then your current database.

About the error ,

you have a identity field in your #TempRandomUser table. when you have a identity field you need to first set the Identity_Insert Off.
otherwise explicity give the column list including the identity column and pass values to them
(eg: insert into tablename(column1,column2) select column1,column2 ..
from tablename)

set IDENTITY_INSERT #TempRandomUser Off before the insert statement should help you.

----------------------------
Anything that Doesn't Kills you Makes you Stronger

Edited by - Nazim on 01/09/2002 03:51:02

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2002-01-09 : 12:59:52

Nazim, you got that backwards... In order to ALLOW inserting an Identity value, you have to Set Identity_Insert ON and then do the insert.

HOWEVER, Mike, you are not supplying a value to be assigned to the identity field, so instead, try just supplying the list of field names in your INSERT INTO statement. Something like this:

INSERT INTO #TempRandomUser (nameonline, votes, points, userid)
SELECT TOP 500 nameOnline, votes, points, userID
FROM tblUserDetails
WHERE active = 1
ORDER BY DATE DESC

--------------------------------------------------------------
1000 Posts, Here I come! I wonder what my new title will be...

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-09 : 16:49:29

You guys are great thanks for the help... Here is the updated SP
Unfortunately I get the following error:
Server: Msg 208, Level 16, State 1, Line 18
Invalid object name '#TempRandomUser'

I have tried every combination of your suggestions to no prevail. Any last suggestions?

Mike

CREATE PROCEDURE GetRandomRecord_RateBodies
(
@genderID tinyint
)
AS

--declare @nRecordCount int
--declare @nRandNum int

-- Create a temporary table with the same structure of , rating, votes, userID
-- the table we want to select a random record from

CREATE TABLE #TempRandomUser

(
nameOnline varchar(15),
votes float(8),
points float(8),
userID int,
idNum int identity(1,1)
)

-- Dump the contents of the table to seach into the
-- temp. table
--INSERT INTO #RandomUser
set identity_insert #TempRandomUser on
IF @genderID > 2
INSERT INTO #TempRandomUser(nameonline, votes, points, userid) SELECT TOP 500 nameOnline, votes, points, userID FROM tblUserDetails WHERE active = 1 ORDER BY DATE DESC
ELSE
INSERT INTO #TempRandomUser(nameonline, votes, points, userid) SELECT TOP 500 nameOnline, votes, points, userID FROM tblUserDetails WHERE genderID = @genderID AND active = 1 ORDER BY DATE DESC
go

--SELECT TOP 500 nameOnline, votes, points, userID FROM tblUserDetails WHERE active = '1' ORDER BY DATE DESC

-- Get the number of records in our temp table
--Select @nRecordCount = count(*) From #TempTable

--@nRecordCount = 500

-- Select a random number between 1 and the number
-- of records in our table
declare @nRandNum int
Select @nRandNum = Round(((498) * Rand() + 1), 0)

-- Select the record from the temp table with the
-- ID equal to the random number selected...
Select nameOnline, votes, points, userID From #TempRandomUser
Where idNum = @nRandNum
Go

JustinBigelow
SQL Gigolo

1157 Posts

Posted - 2002-01-09 : 17:12:25

I maybe wrong (my work cracked down on rogue servers so I can't test until I get home

) but the "go" statements are causing you to start new batches, thus your temp tables are out of scope at the start of the next batch. Try removing the "go"s.

hth,
Justin

Edited by - justinbigelow on 01/09/2002 17:13:08

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-09 : 22:54:21

unfortunately that last suggestion did not work either ...

any last thoughts?

cheers.

Nazim
A custom title

1408 Posts

Posted - 2002-01-10 : 00:44:26

Put the code in one block, with a begin and end .and declare all your variables and Temp table above begin. that should make them accessible to the whole Procedure.

CREATE PROCEDURE GetRandomRecord_RateBodies
(
@genderID tinyint
)
AS

-- Create a temporary table with the same structure of , rating, votes, userID
-- the table we want to select a random record from

CREATE TABLE #TempRandomUser

(
nameOnline varchar(15),
votes float(8),
points float(8),
userID int,
idNum int identity(1,1)
)
declare @nRandNum int

begin

-- Dump the contents of the table to seach into the
-- temp. table
--INSERT INTO #RandomUser
set identity_insert #TempRandomUser on
IF @genderID > 2
INSERT INTO #TempRandomUser(nameonline, votes, points, userid) SELECT TOP 500 nameOnline, votes, points, userID FROM tblUserDetails WHERE active = 1 ORDER BY DATE DESC
ELSE
INSERT INTO #TempRandomUser(nameonline, votes, points, userid) SELECT TOP 500 nameOnline, votes, points, userID FROM tblUserDetails WHERE genderID = @genderID AND active = 1 ORDER BY DATE DESC

--SELECT TOP 500 nameOnline, votes, points, userID FROM tblUserDetails WHERE active = '1' ORDER BY DATE DESC

-- Get the number of records in our temp table
--Select @nRecordCount = count(*) From #TempTable

--@nRecordCount = 500

-- Select a random number between 1 and the number
-- of records in our table
Select @nRandNum = Round(((498) * Rand() + 1), 0)

-- Select the record from the temp table with the
-- ID equal to the random number selected...
Select nameOnline, votes, points, userID From #TempRandomUser
Where idNum = @nRandNum
end
Go

HTH

----------------------------
Anything that Doesn't Kills you Makes you Stronger

mike123
Master Smack Fu Yak Hacker

1462 Posts

Posted - 2002-01-12 : 05:13:34

great thanks that last one worked...

(I took out set identity_insert #tempRandomUser on )

cheers guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources