Solved

Dynamic "where" statement (Stored procedure)

Posted on 2008-06-13
2
354 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
ID: 21779054
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
ID: 21779089
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

895 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

21 Experts available now in Live!

Get 1:1 Help Now