Easy question

Hi,

I have a query that I need to pass as a string as the second parameter of the OpenQuery method. Here's my query:

SELECT * FROM mytable WHERE last_name = 'DOE'

Thing is that a string is set with apostrophies, so I don't know how to set my string since apostrophies are alse in my query. Normally, it would look like this:

DECLARE @.CQUERY VARCHAR(100)
SET @.CQUERY = 'SELECT * FROM mytable WHERE last_name = 'DOE''

But of course this fails. How can I do it then?

Thanks,

Skip.I always do it thid way

DECLARE @.CQUERY VARCHAR(100)
SET @.CQUERY = 'SELECT * FROM mytable WHERE last_name = '+ '''' + 'DOE' + ''''
SELECT @.cquery

Only cause it's easier for me to read.....|||SET @.CQUERY = "SELECT * FROM mytable WHERE last_name = 'DOE'"|||What's the order of the characters (I can't see them correctly with these fonts)?

Is it 2 double quotes, 3 apostrophies, etc.

Thanks again,

Skip|||I do

1 quote text message 1 quoye + 4 quotes + 1 qoute value 1 quote + 4 quotes...

but you should be able to cut and paste the code into QA...|||OK, I've tried a couple of solutions and I can see that something like this works fine:

DECLARE @.CQUERY VARCHAR(100)
SET @.CQUERY = 'SELECT * FROM mytable WHERE last_name = ' + '''' + 'DOE' + ''''

Here, '''' = 4 apostrophies.

Now, I find it wierd that this worked because shoudn't 4 apostrophies open-close empty strings twice (hence creating an error because the + sign is not between them)?

Is this a special case programmed for text appending in SQL Server?

Thanks,

Skip.

dynamic where clause

Hi

I need some advice on which direction to take!

Consider this statement:

SELECT business_name FROM myTable WHERE town = @.town AND county = @.county

My problem is that i will not always have the @.county variable available. Is there a way to use an IF or a CASE inside the SQL statement (i know i can create two seperate sql statments but dont want to do it this way)? If it makes it easier, when the @.county variable is not available, it has a value of 0.

thanks again

Ps, i also know how to do it using dynamic sql using the EXEC() command, but i'd prefer to steer clear of this method also.SELECT business_name
FROM myTable
WHERE (town = @.town OR @.town IS NULL)
AND (county = @.county OR @.county = 0)
?|||Hi pootle

If the @.county has a value of 0 then i don't want to make it part of the WHERE clause, ie i dont want it to search the county column - in your example it would search all counties which are equal to 0?

This is what i'm trying to acheive, but only use one SELECT statment:

IF @.county <> '0'
BEGIN
SELECT business_name FROM myTable WHERE town = @.town AND county = @.county
END
ELSE
BEGIN
SELECT business_name FROM myTable WHERE town = @.town
END|||Hi Mattock

Did you try the code? I know the answer is no :) It does just what you ask for.|||lol, i'm real sorry, got the monday morning blues! Thick mode was well and truly stuck to 'ON'.

cheers again|||No probs :)

Dynamic views

Is it possible to use an IF statement or CASE when creating a view?
I want to something like.
IF Myvariable=n
selelect * from mytable where X=n
Else
selelect * from mytable where X=aYou can use CASE in a view but not IF. You cannot use variables or
parameters in views though. Do this in the WHERE clause when you query
the view.
BTW, don't use SELECT * in views. The results can be unreliable if the
base table changes. List all the required columns by name.
David Portas
SQL Server MVP
--|||no. but you can use stored procedure instead
--
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||Thanks guys, can I call a SPROC from within a view?
"Geo" <noSpamgbarr@.ibigroup.com> wrote in message
news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>|||Nope. If you need a 'parametrized view' you can achieve that by creating a
table function.
What exactly is the purpose of this view?
ML|||> Thanks guys, can I call a SPROC from within a view?
No. A view is no more than a select statement, you can not use variables,
parameters, dml statements other than "select", etc.
Can you tell us what are you trying to accomplish?
AMB
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>|||No. You might try using a table-valued function. Table-valued functions
can't call procs either but they do work quite like views and they can
contain procedural code and make use of parameters. See the CREATE
FUNCTION topic in Books Online.
David Portas
SQL Server MVP
--|||Try out UDF returning a TABLE.
UDF will let you pass parameters & can then be used in the FROM clause of a
SELECT statement same way you use tables & views.
Rakesh
"Geo" wrote:

> Is it possible to use an IF statement or CASE when creating a view?
> I want to something like.
> IF Myvariable=n
> selelect * from mytable where X=n
> Else
> selelect * from mytable where X=a
>
>|||You are missing the concept of a VIEW. It is a virtual table, not a
procedure. Do you expect other tables to change on the fly? And CASE
is an expression, not a statement.|||hi geo,
its the other way around
A stored procedure can call a view
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Geo" wrote:

> Thanks guys, can I call a SPROC from within a view?
> "Geo" <noSpamgbarr@.ibigroup.com> wrote in message
> news:e4jdd5TtFHA.1204@.TK2MSFTNGP15.phx.gbl...
>
>

Dynamic SQL as a DataSet

How do I create a dynamic SQL DataSet?
For Example: 'Select * From MyTable Where MyField In (' + @.S + ')'
(I'm using Oracle as a Database, and thus the variable is :S instead of @.S)Fortunately I found the answer here:
http://solidqualitylearning.com/blogs/dejan/archive/2004/10/22/200.aspx|||On Jun 3, 9:22 am, "=E2=F8=E9 =F8=F9=F3" <GeriRes...@.GMail.com> wrote:
> Fortunately I found the answer here:http://solidqualitylearning.com/blogs=
/dejan/archive/2004/10/22/200.aspx
Glad you found your solution. For future assistance, feel free to
repost on this group.
Regards,
Enrique Martinez
Sr. Software Consultant