asked on

Dynamic SQL statement

How can I build a dynamic SQL statement without inserting the SQL into a variable.

For example the store procedure has a variable called @Sex.

This is my main SQL statement:

Select * FROM MYTABLE
WHERE SSN = @SSN AND AGE=@AGE AND

I would build the rest of the SQL statement using the variable @SEX

@SEX (if male)
TVSHOWS=@TVSHOWS AND RACING=@RACING AND NASCAR=@NASCAR

@SEX (if Female)
SOAPS=@SOAPS AND COOKING=@COOKING AND CHILDREN=@CHILDREN

KGNickl

Make separate queries in a stored procedure. Then just pass in the sex of the person when calling the stored procedure and it will run the correct query for you.

IF @SEX = "MALE"
TVSHOWS=@TVSHOWS AND RACING=@RACING AND NASCAR=@NASCAR

IF @SEX = "MALE"
SOAPS=@SOAPS AND COOKING=@COOKING AND CHILDREN=@CHILDREN

SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Sharath S

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Internet_Engineer

ASKER

Here is the solution:

(@SEX = 'male' AND (TVSHOWS=@TVSHOWS AND RACING=@RACING AND NASCAR=@NASCAR))

(@SEX = 'female' AND (TVSHOWS=@TVSHOWS AND RACING=@RACING AND NASCAR=@NASCAR))

Sharath S

Solution was provided. Not sure why the asker wants to close this question without accepting any expert's post. In fact the solution posted by asker is wrong when checked with what he/she asked.

Sharath S

My recommendation is #3 and I suggest the posts http:#a35183296 and http:#a35183174 as answers.

modus_operandi

Starting auto-close process to implement the recommendations of the participating Expert(s).

modus_operandi
EE Admin