if statement after with

Question

if statement after with

dr_csharp

SSCommitted

Points: 1765
More actions
November 1, 2008 at 2:39 am

#73249

hi friends
whats wrong with this :
CREATE PROCEDURE [dbo].[LoadGalery]
(@rowNumber INT)
AS BEGIN
DECLARE @state BIT
SET @state='0'
WITH Im AS
(
SELECT Id,Name,Tag,ROW_NUMBER() OVER(ORDER BY Name DESC)AS 'RowNumber' FROM Galery
)
IF EXISTS(SELECT * FROM Im WHERE RowNumber=@rowNumber)
SET @state='1'
RETURN @state
END

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Does it throw an error? If so, what error? Does it give unexpected results?

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 2

yes,the error is :

Incorrect syntax near the keyword 'IF'. :O(

the Table is this :

CREATE TABLE [dbo].[Galery](

[Id] [int] IDENTITY(1,1) NOT NULL,

[Name] [nchar](50) NOT NULL CONSTRAINT [DF_Galery_Name] DEFAULT (N'~/Images/Gallery/pic.gif'),

[Tag] [nvarchar](200) NULL,

CONSTRAINT [PK_Galery] PRIMARY KEY CLUSTERED

(

[Id] ASC

)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]

) ON [PRIMARY]

END

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Try this.

DECLARE @state BIT

SET @state='0'

;WITH Im AS (

SELECT Id,Name,Tag,ROW_NUMBER() OVER(ORDER BY Name DESC)AS 'RowNumber' FROM Galery

)

SELECT @State = count(*) FROM Im WHERE RowNumber=@rowNumber

RETURN @state

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

Please post in the correct forum in the future. This is the SQL 2000 forum and CTEs doen't exist on SQL 2000

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 5

GilaMonster (11/1/2008)
Try this.
DECLARE @state BIT
SET @state='0'
;WITH Im AS (
SELECT Id,Name,Tag,ROW_NUMBER() OVER(ORDER BY Name DESC)AS 'RowNumber' FROM Galery
)
SELECT @State = count(*) FROM Im WHERE RowNumber=@rowNumber
RETURN @state

its not good for me,because i need some another field from Im besides count(*) :O(

i mean i need to select max(rownumber) and other fields but you are going to select just count(*)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

dr_csharp (11/1/2008)
its not good for me,because i need some another field from Im besides count(*) :O(
i mean i need to select max(rownumber) and other fields but you are going to select just count(*)

What I posted is identical to what yours would have done were the if allowed. Return either 0 or 1 in the State, depending on whether there were rows or not. There is no way the code you posted could return anything other than the state.

Now, if you want to do something other than what you initially posted, explain, give sample data and give expected results

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

dr_csharp SSCommitted Points: 1765 More actions · Answer 7

please forgive me,i didnt ask completely:

i have a table named Gallery

i wanna select some fields from Gallery base on a specific RowNumber

i wanna return a value that show whether this selected field are belonging to last Row Number or not

thanks alot

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 8

Now things make sense....

CREATE PROCEDURE [dbo].[LoadGalery]

(@rowNumber INT)

AS

WITH Im AS (

SELECT Id,Name,Tag,ROW_NUMBER() OVER(ORDER BY Name DESC)AS RowNumber FROM Galery

)

SELECT id, name, tag,

CASE WHEN RowNumber = (SELECT MAX(RowNumber) AS MaxRow FROM Im) THEN 1 ELSE 0 END AS IsMaxRow

from Im where RowNumber = @RowNumber;

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass