Dynamic SQL another example

In several messages that I post early I need help for create Dynamic SQL or
that I trying to do.
Here is an example of my store procedure
ALTER PROCEDURE dbo.Test
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
SET LANGUAGE us_english
SET NOCOUNT ON
declare @.Colum sysname
declare @.MyDate nvarchar(10)
set @.MyDate = '2004-05-05'
set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
print '---'
print 'The following expression no generate an error but not return me'
print 'anything rows. ( Both are String )'
print '---'
-- Both are String
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
print '=======================================
========================='
print
'----'
print 'Thi following expression generate an errorto have types mismatch).'
print
'----'
-- To have types mismatch.
print 'Expression:'
print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
print 'Result:'
print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
print '=======================================
========================='
SET NOCOUNT OFF
RETURN
-- The out --
Running dbo."Test".
---
The following expression no generate an error but not return me
anything rows. ( Both are String )
---
Expression:
@.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
Result:
FechaMie>=2004-05-05
========================================
========================
----
Thi following expression generate an errorto have types mismatch).
----
Expression:
@.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
Result:
Syntax error converting datetime from character string.
No rows affected.
(0 row(s) returned)
@.RETURN_VALUE =
Finished running dbo."Test".I just posted a reply to this. No need to repost.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.g
bl...
> In several messages that I post early I need help for create Dynamic SQL
or
> that I trying to do.
> Here is an example of my store procedure
> ALTER PROCEDURE dbo.Test
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
> SET LANGUAGE us_english
> SET NOCOUNT ON
> declare @.Colum sysname
> declare @.MyDate nvarchar(10)
> set @.MyDate = '2004-05-05'
> set @.Colum = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),14)
> print '---'
> print 'The following expression no generate an error but not return me'
> print 'anything rows. ( Both are String )'
> print '---'
> -- Both are String
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(nvarchar(10),@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> print '=======================================
========================='
> print
> '----'
> print 'Thi following expression generate an errorto have types mismatch)
.'
> print
> '----'
> -- To have types mismatch.
> print 'Expression:'
> print '@.Colum + ''>='' + CONVERT(DATETIME,@.MyDate,100)'
> print 'Result:'
> print @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> print '=======================================
========================='
> SET NOCOUNT OFF
> RETURN
>
> -- The out --
>
> Running dbo."Test".
> ---
> The following expression no generate an error but not return me
> anything rows. ( Both are String )
> ---
> Expression:
> @.Colum + '>=' + CONVERT(nvarchar(10),@.MyDate,100)
> Result:
> FechaMie>=2004-05-05
> ========================================
========================
>
> ----
> Thi following expression generate an errorto have types mismatch).
> ----
> Expression:
> @.Colum + '>=' + CONVERT(DATETIME,@.MyDate,100)
> Result:
> Syntax error converting datetime from character string.
> No rows affected.
> (0 row(s) returned)
> @.RETURN_VALUE =
> Finished running dbo."Test".
>
>|||The following code represents my problem. It's using Northwind database. You
can copy and paste in the SQL analyzer directly and run so you can see the
problem.
My question is: How Can I build a SQL statement but doing replacement of
certain values on fly.
The causes are error data mismatch.
NOTE: I try sp_executeslq and this entire thing.
ALTER PROCEDURE dbo.StoredProcedure1
/*
(
@.parameter1 datatype = default value,
@.parameter2 datatype OUTPUT
)
*/
AS
set language us_English
set nocount on
declare @.BirthDatePos int
declare @.colum sysname -- sysname is equal to nvarchar(128)
/*
* ----
* When @.DateFrom and @.DateTo are datetime the SQL-Server
* not run. I get the following error message:
* Syntax error converting datetime from character string.
* ----
*/
declare @.DateFrom datetime
declare @.DateTo datetime
/*
* ---
* When @.DateFrom and @.DateTo are both characters the SQL-Server
* run but not return anything.
* ---
*/
--declare @.DateFrom nvarchar(8)
--declare @.DateTo nvarchar(8)
/* Creating my Dynamic SQL: */
set @.DateFrom = '20040505'
set @.DateTo = '20040505'
set @.BirthDatePos = 6
set @.colum = col_name(object_id('Employees'),@.BirthDa
tePos)
SELECT BirthDate
FROM Employees
WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
set nocount off
RETURN
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> I just posted a reply to this. No need to repost.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
or[vbcol=seagreen]
'----'[vbcol=seagreen]
mismatch).'[vbcol=seagreen]
'----'[vbcol=seagreen]
>|||I posted a reply to your other thread about 10 minutes ago.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gb
l...
> The following code represents my problem. It's using Northwind database. Y
ou
> can copy and paste in the SQL analyzer directly and run so you can see the
> problem.
>
> My question is: How Can I build a SQL statement but doing replacement of
> certain values on fly.
>
> The causes are error data mismatch.
>
> NOTE: I try sp_executeslq and this entire thing.
>
>
> ALTER PROCEDURE dbo.StoredProcedure1
> /*
> (
> @.parameter1 datatype = default value,
> @.parameter2 datatype OUTPUT
> )
> */
> AS
>
> set language us_English
> set nocount on
>
> declare @.BirthDatePos int
> declare @.colum sysname -- sysname is equal to nvarchar(128)
>
> /*
> * ----
> * When @.DateFrom and @.DateTo are datetime the SQL-Server
> * not run. I get the following error message:
> * Syntax error converting datetime from character string.
> * ----
> */
> declare @.DateFrom datetime
> declare @.DateTo datetime
>
> /*
> * ---
> * When @.DateFrom and @.DateTo are both characters the SQL-Server
> * run but not return anything.
> * ---
> */
> --declare @.DateFrom nvarchar(8)
> --declare @.DateTo nvarchar(8)
>
> /* Creating my Dynamic SQL: */
> set @.DateFrom = '20040505'
> set @.DateTo = '20040505'
> set @.BirthDatePos = 6
> set @.colum = col_name(object_id('Employees'),@.BirthDa
tePos)
>
> SELECT BirthDate
> FROM Employees
> WHERE (@.colum >= @.DateFrom) AND (@.colum <= @.DateTo)
> set nocount off
> RETURN
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:#plMbyVMEHA.624@.TK2MSFTNGP11.phx.gbl...
> news:eoKhXvVMEHA.2456@.TK2MSFTNGP12.phx.gbl...
> or
> '----'
> mismatch).'
> '----'
>|||Mr. Tibor You code was enough clear I want give thanks to you publicly.
yours advisor was profit.
I resolve my problem....!
MArio
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:#I2FIrfMEHA.2500@.TK2MSFTNGP12.phx.gbl...
> I posted a reply to your other thread about 10 minutes ago.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Mario Reiley" <mreiley@.cantv.net> wrote in message
news:Oh1KT7eMEHA.624@.TK2MSFTNGP11.phx.gbl...
You[vbcol=seagreen]
the[vbcol=seagreen]
in[vbcol=seagreen]
SQL[vbcol=seagreen]
'---'[vbcol=seagreen]
me'[vbcol=seagreen]
'---'[vbcol=seagreen]
'=======================================
========================='[vbcol=seagree
n]
'----'[vbcol=seagreen]
'----'[vbcol=seagreen]
'=======================================
========================='[vbcol=seagree
n]
> ----
> ----
>