VARIAVEL ESTATICA

PARREIRA 05/08/2010 14:36:56
#349533
Olá pessoal.

Tem como eu mantar uma variável estática no sql server 2008,tipo mesmo se o foco sair da função ela continua com o valor que estava antes?
PARREIRA 06/08/2010 11:13:15
#349605
Problema é que uma function não aceita tabela temporaria!
PARREIRA 06/08/2010 12:44:52
#349620
Chamo uma function na minha procedure!
A function que se encarregaria de guardar o valor da variavel!
PARREIRA 06/08/2010 16:17:02
#349652
Eu tenho essa procedure abaixo,nota que deu um select e insiro na tabela Ctrl km,porem o campo CODIGO desta tabela ,não é do tipo identity,então tenho que buscar o ultimo registro que está gravado la ,tipo se codigo 200 o último,eu então iria acresentar 201,202,203,etc...
Porem não estou conseguindo fazer isto,pois ele me retorna o ultimo porém grava o mesmo numero para todos os registros retornados.
Preciso de um jeito de fazer ele somar sempre + 1,mas está dificil...

CREATE PROCEDURE [dbo].[CONTROLE_KM_FUNC_CONTRATO_SP]
AS
DECLARE @ERRO AS VARCHAR(MAX)
DECLARE @CONT AS INT = 1
BEGIN
BEGIN TRY
BEGIN TRANSACTION
Insert Into Tbl_CTRL_KM
(N_CD_CODIGO ,
N_CD_FILIAL,
S_DSC_DATA,
N_CD_FUNCIONARIO,
N_CD_FILIALFUNC,
N_CD_CLIENTE,
N_CD_FILIALCLI,
N_CD_CODCONTR,
N_CD_FILIALCONTR,
N_CD_ITEMCONTR,
N_DSC_KMINICIAL,
N_DSC_KMFINAL,
N_DSC_FRANQUIACLI,
N_DSC_FRANQUIAFUNC,
N_DSC_VALKM,
N_DSC_VALKM_FUNC,
N_DSC_KM,
S_DSC_OBS,
N_CD_USER_ALT,
N_CD_FILIAL_ALT,
S_DSC_DATA_ALT,
S_DSC_HORA_ALT)
SELECT
(SELECT TOP 1 D.N_CD_CODIGO + 1) FROM TBL_Ctrl_KM AS D WHERE D.N_CD_Filial = 1 ORDER BY N_CD_Codigo DESC) AS CODIGO ,
1,
(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102),[ô].[ô],[ô]/[ô])),
CASE WHEN (A.N_CD_TPFUNC = 1 AND A.N_DSC_TPCONTRATO = 1) THEN A.N_CD_FUNCIONARIO
ELSE
0
END AS FUNCIONARIO,
CASE WHEN (A.N_CD_TPFUNC = 1 AND A.N_DSC_TPCONTRATO = 1) THEN A.N_CD_FILIAL
ELSE
0
END AS FILIAL,
B.N_CD_CLIENTE,
dbo.BUSCA_FILIALCLI(B.N_CD_CLIENTE,A.N_CD_FILIALCTRL),
A.N_CD_CODIGO,
A.N_CD_FILIAL,
A.N_CD_Indice,
0,
0,
A.N_Dsc_FranquiaKM,
A.N_Dsc_FranqFunc,
(SELECT REPLACE(A.N_Val_KmExcedPF,[ô],[ô],[ô].[ô])),
(SELECT REPLACE(A.N_Val_PGFuncPF,[ô],[ô],[ô].[ô])),
0,
[ô].[ô],
63,
1,
(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô])),
[ô]01:00:00[ô]

From Tbl_Contrato_Item as A
INNER JOIN Tbl_Contrato as B ON B.N_CD_CodContr = A.N_CD_Codigo And B.N_CD_FilialCtrl = A.N_CD_FilialCtrl
Where A.N_Dsc_TpContrato IN (1,2)
And A.S_Dsc_PgKM = [ô]S[ô]
And A.N_CD_Filial = 1
AND (SELECT COUNT(B.N_CD_CODIGO) FROM TBL_Feriado AS B WHERE B.S_Dsc_Dia = DATEPART(DAY,(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô]))) AND B.S_Dsc_Mes = DATEPART(MONTH,(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô]))) ) <= 0
AND (SELECT COUNT(B.N_CD_CODIGO) FROM TBL_Ponto_Facultativo AS B WHERE B.S_Dsc_Dia = DATEPART(DAY,(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô]))) AND B.S_Dsc_Mes = DATEPART(MONTH,(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô]))) ) <= 0

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @ERRO = ERROR_MESSAGE()
RAISERROR(@ERRO,16,1)
END CATCH
END
PARREIRA 09/08/2010 08:40:28
#349766
Então cara,consegui resolver meu problema,estou postando aqui a minha procedure ,pode ajudar alguem um dia,e valeu pela atenção ....

USE [SGI]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[CONTROLE_KM_FUNC_CONTRATO_SP]
AS
DECLARE @ERRO AS VARCHAR(MAX)
DECLARE @CONT AS INT = NULL
DECLARE @CONTADOR AS INT = (SELECT TOP 1 N_CD_CODIGO FROM TBL_Ctrl_KM WHERE N_CD_Filial = 1 ORDER BY N_CD_Codigo DESC)

BEGIN
BEGIN TRY
BEGIN TRANSACTION

SET NOCOUNT ON
IF EXISTS (SELECT * FROM tempdb.sys.objects WHERE name = [ô]##TBL_TESTE[ô] AND type = [ô]U[ô])
BEGIN
DROP TABLE ##TBL_TESTE
END

CREATE TABLE ##TBL_TESTE(N_CD_CODIGO INT IDENTITY(1 ,1),
N_CD_FILIAL TINYINT,
S_DSC_DATA VARCHAR(10),
N_CD_FUNCIONARIO INT,
N_CD_FILIALFUNC TINYINT,
N_CD_CLIENTE INT,
N_CD_FILIALCLI TINYINT,
N_CD_CODCONTR INT,
N_CD_FILIALCONTR TINYINT,
N_CD_ITEMCONTR TINYINT,
N_DSC_KMINICIAL INT,
N_DSC_KMFINAL INT,
N_DSC_FRANQUIACLI INT,
N_DSC_FRANQUIAFUNC INT,
N_DSC_VALKM MONEY,
N_DSC_VALKM_FUNC MONEY,
N_DSC_KM INT,
S_DSC_OBS NTEXT,
N_CD_USER_ALT TINYINT,
N_CD_FILIAL_ALT TINYINT,
S_DSC_DATA_ALT VARCHAR(10),
S_DSC_HORA_ALT CHAR(8))
SET IDENTITY_INSERT DBO.##TBL_TESTE ON
INSERT INTO ##TBL_TESTE (N_CD_CODIGO)VALUES(@CONTADOR)
SET IDENTITY_INSERT DBO.##TBL_TESTE OFF
Insert Into ##TBL_TESTE
(
N_CD_FILIAL,
S_DSC_DATA,
N_CD_FUNCIONARIO,
N_CD_FILIALFUNC,
N_CD_CLIENTE,
N_CD_FILIALCLI,
N_CD_CODCONTR,
N_CD_FILIALCONTR,
N_CD_ITEMCONTR,
N_DSC_KMINICIAL,
N_DSC_KMFINAL,
N_DSC_FRANQUIACLI,
N_DSC_FRANQUIAFUNC,
N_DSC_VALKM,
N_DSC_VALKM_FUNC,
N_DSC_KM,
S_DSC_OBS,
N_CD_USER_ALT,
N_CD_FILIAL_ALT,
S_DSC_DATA_ALT,
S_DSC_HORA_ALT)
SELECT
1,
(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102),[ô].[ô],[ô]/[ô])),
CASE WHEN (A.N_CD_TPFUNC = 1 AND A.N_DSC_TPCONTRATO = 1) THEN A.N_CD_FUNCIONARIO
ELSE
0
END AS FUNCIONARIO,
CASE WHEN (A.N_CD_TPFUNC = 1 AND A.N_DSC_TPCONTRATO = 1) THEN A.N_CD_FILIAL
ELSE
0
END AS FILIAL,
B.N_CD_CLIENTE,
dbo.BUSCA_FILIALCLI(B.N_CD_CLIENTE,A.N_CD_FILIALCTRL),
A.N_CD_CODIGO,
A.N_CD_FILIAL,
A.N_CD_Indice,
0,
0,
A.N_Dsc_FranquiaKM,
A.N_Dsc_FranqFunc,
(SELECT REPLACE(A.N_Val_KmExcedPF,[ô],[ô],[ô].[ô])),
(SELECT REPLACE(A.N_Val_PGFuncPF,[ô],[ô],[ô].[ô])),
0,
[ô].[ô],
63,
1,
(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô])),
[ô]01:00:00[ô]

From Tbl_Contrato_Item as A
INNER JOIN Tbl_Contrato as B ON B.N_CD_CodContr = A.N_CD_Codigo And B.N_CD_FilialCtrl = A.N_CD_FilialCtrl
Where A.N_Dsc_TpContrato IN (1,2)
And A.S_Dsc_PgKM = [ô]S[ô]
And A.N_CD_Filial = 1
AND (SELECT COUNT(B.N_CD_CODIGO) FROM TBL_Feriado AS B WHERE B.S_Dsc_Dia = DATEPART(DAY,(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô]))) AND B.S_Dsc_Mes = DATEPART(MONTH,(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô]))) ) <= 0
AND (SELECT COUNT(B.N_CD_CODIGO) FROM TBL_Ponto_Facultativo AS B WHERE B.S_Dsc_Dia = DATEPART(DAY,(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô]))) AND B.S_Dsc_Mes = DATEPART(MONTH,(SELECT REPLACE(CONVERT(CHAR,GETDATE(),102 ),[ô].[ô],[ô]/[ô]))) ) <= 0


Insert Into TBL_Ctrl_KM
(N_CD_Codigo,
N_CD_FILIAL,
S_DSC_DATA,
N_CD_FUNCIONARIO,
N_CD_FILIALFUNC,
N_CD_CLIENTE,
N_CD_FILIALCLI,
N_CD_CODCONTR,
N_CD_FILIALCONTR,
N_CD_ITEMCONTR,
N_DSC_KMINICIAL,
N_DSC_KMFINAL,
N_DSC_FRANQUIACLI,
N_DSC_FRANQUIAFUNC,
N_DSC_VALKM,
N_DSC_VALKM_FUNC,
N_DSC_KM,
S_DSC_OBS,
N_CD_USER_ALT,
N_CD_FILIAL_ALT,
S_DSC_DATA_ALT,
S_DSC_HORA_ALT)
SELECT * FROM ##TBL_TESTE

DROP TABLE ##TBL_TESTE

COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
SET @ERRO = ERROR_MESSAGE()
RAISERROR(@ERRO,16,1)
END CATCH
END
Tópico encerrado , respostas não são mais permitidas
Voltar