Dinamic SQL questions

Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
all the time I recive the following error:
Syntax error converting datetime from character string. Here is my SQL:
declare @.sql nvarchar(1024)
declare @.Desde int,@.Hasta int
declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
htDetalleNormal.HojaId, ' +
'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS Hasta
' +
'FROM personal FULL OUTER JOIN ' +
'Clasificacion ON personal.ClasificacionId = Clasificacion.ClasificacionId FULL OUTER JOIN ' +
'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
OUTER JOIN ' +
'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId '
+
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >= CONVERT(DATETIME,@.FDesde, 102)) ' +
'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <= CONVERT(DATETIME,@.FHasta, 102)) for browse'
set @.Desde = 14
set @.Hasta = 14
set @.FDesde = '2004-08-01'
set @.FHasta = '2004-08-01'
exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
nchar',
@.Desde,@.Hasta,@.FDesde,@.FHasta
Well Any advisor is Wellcome
Thank
MarioUse a language neutral datetime format in your CONVERTs and your assignments to @.FDesde and @.FHasta. See below
for info:
http://www.karaszi.com/sqlserver/info_datetime.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Mario Reiley" <mreiley@.cantv.net> wrote in message news:%23C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId => Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId '
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>|||Mario,
I'm a very beginner in SQL and probably I give you a wrong advice (sorry if
so), but I think you have to exclude your parameters from strings, so
instead of
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=CONVERT(DATETIME,@.FDesde, 102)) '
I would write:
'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),' + @.Desde+N') >=CONVERT(DATETIME,'+@.FDesde+N', 102)) '
I have a problem with a dynamic SQL too, I cannot write correct where clause
with using BETWEEN for dates. My syntax doesn't produce any error, it just
doesn't return anything.
I have this:
SET @.sql = N'SELECT dbo.[Partial].PartialNumber,
dbo.CommissionPayments.CheckDate, dbo.CommissionPayments.CheckNumber,
dbo.CommissionPayments.CommissionAmountPaid,
dbo.Salesman.Salesman, dbo.CommissionPayments.CheckAmount
FROM dbo.[Partial] RIGHT OUTER JOIN
dbo.Salesman RIGHT OUTER JOIN
dbo.CommissionPayments ON dbo.Salesman.SalesmanID =dbo.CommissionPayments.SalesmanID ON
dbo.[Partial].PartialID =dbo.CommissionPayments.PartialID
WHERE (dbo.CommissionPayments.RowDeleted <> 1)'
SET @.sql = @.sql + N' AND (dbo.CommissionPayments.CheckDate BETWEEN
CONVERT(DATETIME, '+@.DateMin+N') AND
CONVERT(DATETIME, '+@.DateMax+N'))'
Maybe you can see something wrong with my syntax?
Vlad
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId => Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>|||Mario, try this. We have to figure out the correct column names at an
earlier stage.
declare @.DesdeColName sysname
declare @.HastaColName sysname
-- Figure out the correct column names first
set @.DesdeColName = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Desde)
set @.HastaColName = COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Hasta)
-- Set the where-clause
set @.sql = @.sql +
'WHERE ' + @.DesdeColName + ' >= CONVERT(DATETIME,@.FDesde, 102)) ' +
'AND ' + @.HastaColName + ' <= CONVERT(DATETIME,@.FHasta, 102))'
-- Display the full sql statement so we can run it in Query Analyzer
print @.sql
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:%23C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId => Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>|||Well I thing that problem is when the SQL SERVER or ADO Library Parsen my
expression:
COL_NAME(OBJECT_ID('dbo.HtDetalleNormal'),@.Desde) + ' >= ' +
CONVERT(DATETIME,@.FDesde)
Note: @.Desde,@.FHasta both are variables int and dbo.HtDetalleNormal is a the
table's name.
The relational algebra not is complaining with the rules. Because when the
COL_NAME(OBJECT_ID('dbo.HtDetalleNormal') Constructs return a sysname object
and then sysname equal to nvarchar(128).
And I don't know which might be another form or way for construct my Dynamic
SQL.
May be somebody know. Any idea is welcome.
Best regard
MArio
"Mario Reiley" <mreiley@.cantv.net> wrote in message
news:#C8SJhQMEHA.1032@.tk2msftngp13.phx.gbl...
> Hi , group I am trying to create a SQL dinamicaly in a Store Procedure but
> all the time I recive the following error:
> Syntax error converting datetime from character string. Here is my SQL:
> declare @.sql nvarchar(1024)
> declare @.Desde int,@.Hasta int
> declare @.FDesde nvarchar(10),@.FHasta nvarchar(10)
> set @.sql = N'SELECT htDetalleNormal.Lun, htDetalleNormal.Mar,
> htDetalleNormal.Mie, htDetalleNormal.Jue, htDetalleNormal.Vie,
> htDetalleNormal.HojaId, ' +
> 'htDetalleNormal.FechaMie AS Desde, htDetalleNormal.FechaMie AS
Hasta
> ' +
> 'FROM personal FULL OUTER JOIN ' +
> 'Clasificacion ON personal.ClasificacionId => Clasificacion.ClasificacionId FULL OUTER JOIN ' +
> 'Hojatiempo ON personal.CedulaId = Hojatiempo.CedulaId FULL
> OUTER JOIN ' +
> 'htDetalleNormal ON Hojatiempo.HojaId = htDetalleNormal.HojaId
'
> +
> 'WHERE (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Desde) >=> CONVERT(DATETIME,@.FDesde, 102)) ' +
> 'AND (COL_NAME(OBJECT_ID(''dbo.HtDetalleNormal''),@.Hasta) <=> CONVERT(DATETIME,@.FHasta, 102)) for browse'
>
> set @.Desde = 14
> set @.Hasta = 14
> set @.FDesde = '2004-08-01'
> set @.FHasta = '2004-08-01'
> exec sp_executesql @.sql ,N'@.Desde int,@.Hasta int,@.FDesde nchar,@.FHasta
> nchar',
> @.Desde,@.Hasta,@.FDesde,@.FHasta
> Well Any advisor is Wellcome
> Thank
> Mario
>