dynamic sql with char(39)

hi,
What's the pros and cons for the following two methods
when you define charactor strings in a dynamic sql?
1.
SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + char(39)
+ '000000' + char(39) ...
2.
SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ...
they both work, I personally prefer second method, what do
you think?
many thanks!!
JJ
I use the second method most of the time. But occassionally when I have
some complex and requires many single qoute, and I am having problems with
the quoting I will consider using the char(39).
----
-
Need SQL Server Examples check out my website
http://www.geocities.com/sqlserverexamples
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:455a01c4904c$370c3e40$a301280a@.phx.gbl...
> hi,
> What's the pros and cons for the following two methods
> when you define charactor strings in a dynamic sql?
> 1.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + char(39)
> + '000000' + char(39) ...
> 2.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ...
> they both work, I personally prefer second method, what do
> you think?
> many thanks!!
> JJ
>
|||thanks Gregory! so there is no performance or reliability
difference between the two?
JJ
>--Original Message--
>I use the second method most of the time. But
occassionally when I have
>some complex and requires many single qoute, and I am
having problems with
>the quoting I will consider using the char(39).
>--
>----
--
>----
--
>-
>Need SQL Server Examples check out my website
>http://www.geocities.com/sqlserverexamples
>
>"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:455a01c4904c$370c3e40$a301280a@.phx.gbl...
+ ''000000'' ...[vbcol=seagreen]
do
>
>.
>
|||Or you can use this #3.
SELECT @.EXPORT_VIEW_SQL = 'SELECT ' + quotename('000000',char(39))
"JJ Wang" <anonymous@.discussions.microsoft.com> wrote in message
news:455a01c4904c$370c3e40$a301280a@.phx.gbl...
> hi,
> What's the pros and cons for the following two methods
> when you define charactor strings in a dynamic sql?
> 1.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + char(39)
> + '000000' + char(39) ...
> 2.
> SELECT @.EXPORT_VIEW_SQL = ... 'SELECT ' + ''000000'' ...
> they both work, I personally prefer second method, what do
> you think?
> many thanks!!
> JJ
>

Dynamic SQL or Table Variables?

Hi all,
I was wondering if anyone knew all the pros and cons about using table
variables instead of dynamic SQL, and vice versa'
Problem i have, is writing a stored procedure which allows users to
search for particular records in the database. This is fine when all
the columns are nvarchar, but when it also involves Id fields, its a
pain.
Example
tblCompany
Id - int
Name - nvarchar
Status - nvarchar
SalespersonId - int
I would like users to be able to search on Name, Status, SalespersonId
For the string fields i use something like this.
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
But the user may or maynot have entered a SalespersonId. If they have
then i want to return records with that particular Id.
I was thinking of using the typical dynamic SQL approach as follows :
@.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
IF @.NAME IS NOT NULL
SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
IF @.STATUS IS NOT NULL
SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
IF @.SALESPERSONID IS NOT NULL
SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
Then i thought about using the newish table variables, so i would
define the table and then run the following.
' CREATE TABLE VARIABLE HERE
INSERT INTO TABLE_VARIABLE
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
IF @.SALESPERSONID IS NOT NULL
DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
What do you all think'Hi
http://www.sommarskog.se/dyn-search.html
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1163671811.306877.294670@.h54g2000cwb.googlegroups.com...
> Hi all,
> I was wondering if anyone knew all the pros and cons about using table
> variables instead of dynamic SQL, and vice versa'
> Problem i have, is writing a stored procedure which allows users to
> search for particular records in the database. This is fine when all
> the columns are nvarchar, but when it also involves Id fields, its a
> pain.
> Example
> tblCompany
> Id - int
> Name - nvarchar
> Status - nvarchar
> SalespersonId - int
> I would like users to be able to search on Name, Status, SalespersonId
> For the string fields i use something like this.
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> But the user may or maynot have entered a SalespersonId. If they have
> then i want to return records with that particular Id.
> I was thinking of using the typical dynamic SQL approach as follows :
> @.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
> IF @.NAME IS NOT NULL
> SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
> IF @.STATUS IS NOT NULL
> SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
> IF @.SALESPERSONID IS NOT NULL
> SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
> Then i thought about using the newish table variables, so i would
> define the table and then run the following.
> ' CREATE TABLE VARIABLE HERE
> INSERT INTO TABLE_VARIABLE
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> IF @.SALESPERSONID IS NOT NULL
> DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
> What do you all think'
>

Dynamic SQL or Table Variables?

Hi all,
I was wondering if anyone knew all the pros and cons about using table
variables instead of dynamic SQL, and vice versa?
Problem i have, is writing a stored procedure which allows users to
search for particular records in the database. This is fine when all
the columns are nvarchar, but when it also involves Id fields, its a
pain.
Example
tblCompany
Id - int
Name - nvarchar
Status - nvarchar
SalespersonId - int
I would like users to be able to search on Name, Status, SalespersonId
For the string fields i use something like this.
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
But the user may or maynot have entered a SalespersonId. If they have
then i want to return records with that particular Id.
I was thinking of using the typical dynamic SQL approach as follows :
@.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
IF @.NAME IS NOT NULL
SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
IF @.STATUS IS NOT NULL
SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
IF @.SALESPERSONID IS NOT NULL
SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
Then i thought about using the newish table variables, so i would
define the table and then run the following.
' CREATE TABLE VARIABLE HERE
INSERT INTO TABLE_VARIABLE
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
IF @.SALESPERSONID IS NOT NULL
DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
What do you all think?
Hi
http://www.sommarskog.se/dyn-search.html
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1163671811.306877.294670@.h54g2000cwb.googlegr oups.com...
> Hi all,
> I was wondering if anyone knew all the pros and cons about using table
> variables instead of dynamic SQL, and vice versa?
> Problem i have, is writing a stored procedure which allows users to
> search for particular records in the database. This is fine when all
> the columns are nvarchar, but when it also involves Id fields, its a
> pain.
> Example
> tblCompany
> Id - int
> Name - nvarchar
> Status - nvarchar
> SalespersonId - int
> I would like users to be able to search on Name, Status, SalespersonId
> For the string fields i use something like this.
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> But the user may or maynot have entered a SalespersonId. If they have
> then i want to return records with that particular Id.
> I was thinking of using the typical dynamic SQL approach as follows :
> @.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
> IF @.NAME IS NOT NULL
> SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
> IF @.STATUS IS NOT NULL
> SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
> IF @.SALESPERSONID IS NOT NULL
> SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
> Then i thought about using the newish table variables, so i would
> define the table and then run the following.
> ' CREATE TABLE VARIABLE HERE
> INSERT INTO TABLE_VARIABLE
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> IF @.SALESPERSONID IS NOT NULL
> DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
> What do you all think?
>

Dynamic SQL or Table Variables?

Hi all,
I was wondering if anyone knew all the pros and cons about using table
variables instead of dynamic SQL, and vice versa'
Problem i have, is writing a stored procedure which allows users to
search for particular records in the database. This is fine when all
the columns are nvarchar, but when it also involves Id fields, its a
pain.
Example
tblCompany
Id - int
Name - nvarchar
Status - nvarchar
SalespersonId - int
I would like users to be able to search on Name, Status, SalespersonId
For the string fields i use something like this.
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
But the user may or maynot have entered a SalespersonId. If they have
then i want to return records with that particular Id.
I was thinking of using the typical dynamic SQL approach as follows :
@.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
IF @.NAME IS NOT NULL
SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
IF @.STATUS IS NOT NULL
SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
IF @.SALESPERSONID IS NOT NULL
SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
Then i thought about using the newish table variables, so i would
define the table and then run the following.
' CREATE TABLE VARIABLE HERE
INSERT INTO TABLE_VARIABLE
SELECT * FROM TBLCOMPANY
WHERE (
CASE
WHEN @.NAME <> '' THEN NAME
WHEN @.STATUS <> '' THEN STATUS
END
LIKE
CASE
WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
END)
IF @.SALESPERSONID IS NOT NULL
DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
What do you all think'Hi
http://www.sommarskog.se/dyn-search.html
"Nemisis" <darrens2005@.hotmail.com> wrote in message
news:1163671811.306877.294670@.h54g2000cwb.googlegroups.com...
> Hi all,
> I was wondering if anyone knew all the pros and cons about using table
> variables instead of dynamic SQL, and vice versa'
> Problem i have, is writing a stored procedure which allows users to
> search for particular records in the database. This is fine when all
> the columns are nvarchar, but when it also involves Id fields, its a
> pain.
> Example
> tblCompany
> Id - int
> Name - nvarchar
> Status - nvarchar
> SalespersonId - int
> I would like users to be able to search on Name, Status, SalespersonId
> For the string fields i use something like this.
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> But the user may or maynot have entered a SalespersonId. If they have
> then i want to return records with that particular Id.
> I was thinking of using the typical dynamic SQL approach as follows :
> @.SQL = 'SELECT * FROM TBLCOMPANY WHERE '
> IF @.NAME IS NOT NULL
> SET @.SQL = '(NAME LIKE ' + @.NAME + ')'
> IF @.STATUS IS NOT NULL
> SET @.SQL = '(STATUS LIKE ' + @.STATUS + ')'
> IF @.SALESPERSONID IS NOT NULL
> SET @.SQL = '(SALESPERSONID = ' + @.SALESPERSONID + ')'
> Then i thought about using the newish table variables, so i would
> define the table and then run the following.
> ' CREATE TABLE VARIABLE HERE
> INSERT INTO TABLE_VARIABLE
> SELECT * FROM TBLCOMPANY
> WHERE (
> CASE
> WHEN @.NAME <> '' THEN NAME
> WHEN @.STATUS <> '' THEN STATUS
> END
> LIKE
> CASE
> WHEN @.NAME <> '' THEN '%' + @.NAME + '%'
> WHEN @.STATUS <> '' THEN '%' + @.STATUS + '%'
> END)
> IF @.SALESPERSONID IS NOT NULL
> DELETE FROM TABLE_VARIABLE WHERE SALESPERSONID <> @.SALESPERSONID
> What do you all think'
>