Solved

Dynamic "where" statement (Stored procedure)

Posted on 2008-06-13
2
353 Views
Last Modified: 2008-06-13
Se attached code snippet. I need help to make the "whereStatement" correct.

If @guid is NOT blank I want the where statement to be like this:
where active='1'  and guid=@guid

If @guid is blank I want the where statement to be like this:
where active='1'  and codeID=@codeID

Thanks for all help :)
ALTER PROCEDURE [dbo].[spKodeDetaljPay] 

(

@codeid smallint,

@guid varchar(64)

)

AS
 

BEGIN
 

Select title, text, 

(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,

(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode

where active='1' -- and " & whereStatement 
 

END

Open in new window

0
Comment
Question by:webressurs
2 Comments
 
LVL 6

Accepted Solution

by:
Compaq_Engineer earned 125 total points
Comment Utility
Try;-
ALTER PROCEDURE [dbo].[spKodeDetaljPay]
(
@codeid smallint,
@guid varchar(64)
)
AS
 
BEGIN
 
Select title, text,
(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name,
(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode
where
(active='1' and guid=@guid)
OR
(active='1' and codeid=@codeid and @guid='')
 
END
0
 
LVL 16

Assisted Solution

by:brad2575
brad2575 earned 125 total points
Comment Utility
have to set up a query into a string and execute the string

DECLARE @query as varchar(MAX)
 

Set @query = 'Select title, text, ' +

	'(Select name from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as Name, ' +

	'(Select email from tblPersonPay where tblCode.PersonID = tblPersonPay.PersonID) as email from tblCode ' +

	'where active=''1''  '
 

IF @guid = '' OR @guid IS NULL

    Set @query = @query + 'AND guid=''' + @guid + ''' '

ELSE 

	Set @query = @query + 'AND codeID=''' + @codeID  + ''' '
 

EXEC(@query)

Open in new window

0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SP to delete duplicates 15 54
Access left join query 5 30
SQL Query Syntax Error 9 29
complicated query 15 41
In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
As they say in love and is true in SQL: you can sum some Data some of the time, but you can't always aggregate all Data all the time! Introduction: By the end of this Article it is my intention to bring the meaning and value of the above quote to…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now