Declare scalar variable in Stored Procedure

Question

Declare scalar variable in Stored Procedure

Navigation

#1 by (0 votes)
#2 by (0 votes)

1

I have the following SP and it marks me the following error in the SP:

SQLState = 37000, NativeError = 137 Error = [Microsoft] [SQL Server]   Native Client 10.0] [SQL Server] Must declare the scalar variable   "@Today". SQLState = 37000, NativeError = 8180 Error = [Microsoft] [SQL   Server Native Client 10.0] [SQL Server] Statement (s) could not be   prepared.

Must declare the scalar variable

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE consulta_diaria2
@Hoy DATE
AS
BEGIN


SET NOCOUNT ON;

SELECT @Hoy = GETDATE()
-- Insert statements for procedure here
--DECLARE @archivo varchar(50)

DECLARE @comando varchar(2000)
DECLARE @query   VARCHAR(2000)


SET @query   = 'bcp "SELECT c.CodeCompany, @Hoy AS fechaActual, a.idClave, c.nombre +''-''+c.apellidos AS Nombre,d.Descripcion AS Depto,t.HorasAlDia AS HorasDia,convert(time(0),fechaEntrada) AS Entrada,convert(time(0),fechaSalida) AS Salida,DATEDIFF(HOUR,fechaEntrada,fechaSalida) %24 AS horasAlDia FROM tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave JOIN tblTurno t ON t.idTurno=c.fkTurno JOIN tblDepto d ON c.fkDepto=d.idDepto WHERE fechaEntrada BETWEEN @Hoy AND DATEADD(DAY,1,@Hoy) and fkStatus !=1" queryout E:\archivo.txt -c -T'


SET @comando = @query  + ' -Usa -P123456'



EXEC master..xp_cmdshell @comando


END
GO

sql sql-server sql-server-2008-r2

asked by Noel L 15.01.2018 в 17:39

source

2 answers

Install snap linux application to run it without privileges Problem connection form to the mysql php database [closed]

score 0 · Answer 1

Modify this line in set @query

SET @query   = 'bcp "SELECT c.CodeCompany, ' + @Hoy +' AS fechaActual, a.idClave, c.nombre +''-''+c.apellidos AS Nombre,d.Descripcion AS Depto,t.HorasAlDia AS HorasDia,convert(time(0),fechaEntrada) AS Entrada,convert(time(0),fechaSalida) AS Salida,DATEDIFF(HOUR,fechaEntrada,fechaSalida) %24 AS horasAlDia FROM tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave JOIN tblTurno t ON t.idTurno=c.fkTurno JOIN tblDepto d ON c.fkDepto=d.idDepto WHERE fechaEntrada BETWEEN ' +  @Hoy + ' AND DATEADD(DAY,1, ' + @Hoy + ' ) and fkStatus !=1" queryout E:\archivo.txt -c -T'

score 0 · Answer 2

The problem occurs because the variable @query has no way of knowing how the variable @hoy can be concatenated and it is also out of its current context. To solve it you will have to modify the variable @query and directly using the function GETDATE() :

SET @query   = 'bcp "SELECT c.CodeCompany, GETDATE() AS fechaActual, a.idClave, c.nombre +''-''+c.apellidos AS Nombre,d.Descripcion AS Depto,t.HorasAlDia AS HorasDia,convert(time(0),fechaEntrada) AS Entrada,convert(time(0),fechaSalida) AS Salida,DATEDIFF(HOUR,fechaEntrada,fechaSalida) %24 AS horasAlDia FROM tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave JOIN tblTurno t ON t.idTurno=c.fkTurno JOIN tblDepto d ON c.fkDepto=d.idDepto WHERE fechaEntrada BETWEEN @Hoy AND DATEADD(DAY,1,@Hoy) and fkStatus !=1" queryout E:\archivo.txt -c -T'

The complete code would be as follows:

CREATE PROCEDURE consulta_diaria2
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @comando VARCHAR(2000)
    DECLARE @query VARCHAR(2000)

    SET @query   = 'bcp "SELECT c.CodeCompany, GETDATE() AS fechaActual, a.idClave, c.nombre +''-''+c.apellidos AS Nombre,d.Descripcion AS Depto,t.HorasAlDia AS HorasDia,convert(time(0),fechaEntrada) AS Entrada,convert(time(0),fechaSalida) AS Salida,DATEDIFF(HOUR,fechaEntrada,fechaSalida) %24 AS horasAlDia FROM tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave JOIN tblTurno t ON t.idTurno=c.fkTurno JOIN tblDepto d ON c.fkDepto=d.idDepto WHERE fechaEntrada BETWEEN @Hoy AND DATEADD(DAY,1,@Hoy) and fkStatus !=1" queryout E:\archivo.txt -c -T'
    SET @comando = @query + ' -Usa -P123456'

    EXEC master..xp_cmdshell @comando
END
GO