Stored Procedure, how to write an IF THEN

[ironhide1975]

Question for ya, how do I modify this to if I pass a 1 in the PreviewCode variable it makes it

Posted = ‘1’

But if PreviewCode = 0 then is shows

Posted = ‘1 or 0’

USE [DatabaseName]

GO

/****** Object: StoredProcedure [dbo].[sp_GetPages] Script Date: 09/10/2007 13:52:52 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[sp_GetPages](

@GrabStart varchar(1000) = NULL,

@GrabEnd varchar(1000) = NULL,

@Position varchar(1000) = NULL,

@PageID int = NULL,

@PreviewCode varchar(1000) = NULL

)

AS

Select * From vwPageDesc

WHERE (DateOn -1) < @GrabStart AND (DateOff +1) > @GrabEnd AND Position = @Position AND fkPageInfo = @PageID AND Active = 1 AND Posted = @PreviewCode ORDER by ContentOrder, DateOn Desc, DateOff Desc

 

[gmmastros]

try this....

ALTER PROCEDURE [dbo].[sp_GetPages](
@GrabStart varchar(1000) = NULL,
@GrabEnd varchar(1000) = NULL,
@Position  varchar(1000) = NULL,
@PageID int = NULL,
@PreviewCode  varchar(1000) = NULL
)

AS

Select * 
From   vwPageDesc
WHERE  (DateOn -1) <  @GrabStart 
       AND (DateOff +1) > @GrabEnd 
       AND Position = @Position 
       AND fkPageInfo = @PageID 
       AND Active = 1 
       AND (
           (@Previewcode = '1' and Posted = 1)
           Or
           (@PreviewCode = '0' And Posted In (0,1))
           )
ORDER by ContentOrder, DateOn Desc, DateOff Desc

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ironhide1975]

Many many thanks!

 

[SQLDenis]

George this can be simplified

assuming you have no negative values for Posted

and Posted <= Previewcode 
[code]

if you do have negative values then

[code]
and Posted between 0 and Previewcode

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[gmmastros]

Denis,

It wasn't clear (from the original post) what other possible values there can be.

In fact, it looks as if Posted can always be 1, but can only (also) be 0 if @PreviewCode is 0.

WHERE  (DateOn -1) <  @GrabStart
       AND (DateOff +1) > @GrabEnd
       AND Position = @Position
       AND fkPageInfo = @PageID
       AND Active = 1
       AND (
           Posted = 1
           Or @PreviewCode = '0' And Posted =0
           )

Besides, the requirement is...

Question for ya, how do I modify this to if I pass a 1 in the PreviewCode variable it makes it

Posted = ‘1’

But if PreviewCode = 0 then is shows

Posted = ‘1 or 0’

So..
[tt][blue]
@PreviewCode Posted
------------ ------
1 1
0 1 or 0
[/blue][/tt]

You wrote...
[tt][blue]and Posted <= @Previewcode [/blue][/tt]

If @PreviewCode = 1, then this would allows Posted to be 0 or 1, which is not what the OP was asking for.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

mmmm, I reversed it

then you would flip the sign

and Posted >= @Previewcode

or if you have values greater than 1

and Posted between @Previewcode and 1

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions