Parameterized Expression: procedure

Showing posts with label procedure. Show all posts

Friday, March 30, 2012

Part of SP not executing... Maybe BUG!?

Hello!

I have a strange problem in one SP. One small part of it wan't execute.

I have pasted whole procedure here and a solution to problem.

So, black on red part of code will not execute if black on blue code is in place.

But, if I supstitute black on blue with black on green, black on red part will execute!

Only thing I want to know is why is that hapening...

Regards

--SNIP--

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FINKO_Knjizenje_Robnih]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FINKO_Knjizenje_Robnih]
GO

CREATE procedure sp_FINKO_Knjizenje_Robnih
(
@.VrDok varchar(8) = '', --Vrsta robnog dokumenta
@.IDs varchar(2200) = '' --IDovi iz HeRPuSPrijenos_Knjizenja
)
AS
BEGIN

set @.VrDok = rtrim(ltrim(@.VrDok))
set @.Ids = rtrim(ltrim(@.Ids))

if @.VrDok='' OR len(@.IDs) = 0
BEGIN
RAISERROR ('Potrebna vrsta dokumenta i IDovi', 16, 1)
return
END
declare @.vbCrLf char(2)
declare @.SQL_temp nvarchar(4000)

set @.vbcrlf = CHAR(13) + CHAR(10)

CREATE TABLE #tmpDokumenti (
[ID] [numeric] (18,0), [Dok_broj] [varchar] (32) COLLATE Croatian_CI_AS NULL, [AK_Skladiste] [varchar] (10) COLLATE Croatian_CI_AS NULL, [Dok_Vrsta] [varchar] (8)COLLATE Croatian_CI_AS NULL
)

set nocount ON
--Racunamo da imamo vrstu dokumenta te ajdiove te cemo sada brojeve dokumenata i skladista prebaciti u temp tablicu
set @.SQL_temp= 'SELECT ID,Dok_Broj,AK_Skladiste,Dok_AK FROM HeRPuSPrijenos_Knjizenja' + @.vbcrlf
set @.SQL_temp= @.SQL_Temp + 'WHERE (isnull(Temeljnica_Nastala,0)=0 AND Dok_OA = ''50'' AND Dok_AK=''' + @.VrDok + ''')' + @.vbcrlf
set @.SQL_temp= @.SQL_Temp + 'AND ID IN (' + @.IDs + ') '

set @.SQL_temp= 'SET NOCOUNT ON ' + @.vbcrlf + @.SQL_temp

INSERT INTO #tmpDokumenti
exec (@.SQL_temp) --prebacujemo

SELECT v.oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,sum(v.Duguje) as Duguje
,sum(v.Potra?uje) as Potra?uje
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potra?uje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo, IDENTITY(int, 1,1) as rbr
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,v.RVrDok as r_Vrsta_dokumenta
INTO #tmpTemeljnica
FROM vFK_RK as V INNER JOIN #tmpDokumenti as D
ON v.broj_dokumenta=d.dok_Broj AND V.AK_skladiste=D.AK_skladiste AND V.vrsta_dokumenta=@.VrDok
GROUP BY v.Oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potra?uje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,v.RVrDok--, v.RK_ID
,case --grupiranje po predznaku...
WHEN v.duguje < 0 THEN 'M'
WHEN v.potra?uje < 0 THEN 'M'
ELSE 'P'
end
IF @.@.ERROR <>0
BEGIN
PRINT 'Greska: sp_FINKO_Knjienje_Robnih'
END

declare @.Greska as bit
declare @.ID_Knjiz as bigint
declare @.VrDok1 as varchar(8)
declare @.BrDok1 as varchar(8)
declare @.Skl as varchar(8)
declare @.Anal1 as varchar(8)
declare @.Anal2 as varchar(8)
declare @.Konto as varchar(8)
declare @.Duguje as numeric(18,2)
declare @.Potrazuje as numeric(18,2)
declare @.SQL_Update as varchar(4000)

SET @.Greska = 0 --ovdje pratimo jeli bilo kakvih gresaka a temeljnici

DECLARE rsTmp CURSOR LOCAL FAST_FORWARD READ_ONLY
FOR
SELECT d.ID,d.ak_skladiste,t.r_Vrsta_dokumenta,t.Broj_dokumenta,t.analiticki_konto,t.podanaliticki_konto,t.Konto,t.Duguje,t.Potra?uje
FROM #tmpTemeljnica as t INNER JOIN #tmpDokumenti as d
ON t.r_vrsta_dokumenta=d.Dok_Vrsta and t.broj_dokumenta=d.Dok_broj
WHERE
(len(t.oznaka_analitike) = 2 and t.analiticki_konto is null)
OR
(len(t.Podoznaka_analitike) = 2 and t.Podanaliticki_konto is null)
OR
(t.Konto is null)
OR
(t.duguje=0 and t.potra?uje=0)
ORDER by d.ID

OPEN rsTmp FETCH NEXT FROM rsTMP into @.ID_Knjiz ,@.Skl,@.VrDok1,@.BrDok1,@.anal1,@.Anal2,@.Konto,@.Duguje,@.Potrazuje
WHILE (@.@.fetch_status=0)
BEGIN
IF @.Anal1 IS NULL
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@.ID_knjiz,'Robni prijenos',0,0,201,'Ne mogu pronaci analitiku za konto','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @.Greska=1
END
ELSE IF @.Anal2 IS NULL
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@.ID_knjiz,'Robni prijenos',0,0,202,'Ne mogu pronaci podanalitiku za konto','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @.Greska=1
END
ELSE IF @.Konto IS NULL
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@.ID_knjiz,'Robni prijenos',0,0,203,'Ne mogu pronaci konto','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @.Greska=1
END
ELSE IF @.Duguje=0 AND @.Potrazuje=0
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(@.ID_knjiz,'Robni prijenos',0,0,204,'Stavka za duguje i potrazuje ima 0','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @.Greska=1
END
FETCH NEXT FROM rsTMP into @.ID_Knjiz ,@.Skl,@.VrDok1,@.BrDok1,@.anal1,@.Anal2
END
CLOSE rsTmp
deallocate rsTmp

if @.Greska= 0
BEGIN
declare @.PojedinacniPrijenos bit
declare @.Tem_vrsta varchar(8) --vrsta tekuce temeljnice
declare @.Tem_broj bigint --sljedeci broj iz fin kartica
declare @.Tem_Datum smalldatetime --danasnji datum
declare @.Tem_ID uniqueidentifier --eto
declare @.Knjiz_Mjesec numeric(18,0)
declare @.Knjiz_Godina numeric(18,0)
declare @.Knjiz_Dnevnik numeric(18,0)

SET @.PojedinacniPrijenos=(
select isnull(osiguranje,0) FROM analiticka_konta where oznaka_analitike='50' and analiticki_konto=@.VrDok
)
SET @.Tem_vrsta =(
select top 1 vrsta_temeljnice from #tmpTemeljnica
)
SET @.Tem_broj =(
SELECT isnull(max(dbo.ToNumeric(FK.Broj_temeljnice)),0) +1 --bilo sranje jer je broj_temeljnice varchar pa je sad ToNumeric
FROM Financijske_kartice as FK WHERE FK.Vrsta_temeljnice=@.Tem_Vrsta
)
SET @.Tem_Datum=CONVERT(CHAR(8),getdate(),112) --treba nam samo datum
SET @.Tem_ID=newid()
SET @.Knjiz_Mjesec=(
select isnull(podatak,0) from fink where segment='KNJIZIFIN' and naziv= 'Mjesec knji?enja'
)
SET @.Knjiz_Godina=(
select isnull(podatak,0) from fink where segment='KNJIZIFIN' and naziv= 'Godina knji?enja'
)
SET @.Knjiz_Dnevnik=(
select isnull(podatak,0) from fink where segment='KNJIZIFIN' and naziv= 'Broj dnevnika'
)

UPDATE #tmpTemeljnica SET
Broj_temeljnice =@.Tem_broj
,Tem_ID =@.Tem_ID
,Datum_temeljnice =@.Tem_datum
,Mjesec =@.Knjiz_Mjesec
,Godina =@.Knjiz_Godina
,Broj_dnevnika =@.Knjiz_Dnevnik

--idemo si napravit kopiju temp tablice(treba nam zbog grupnoh odnosno pojedinacnog prijenosa)
SELECT * INTO #tmpTemeljnica1
FROM #tmpTemeljnica WHERE 1=2

--idemo sad grupirat sve sto treba
SET @.PojedinacniPrijenos =1 --zasad ostajemo na ovom

IF @.PojedinacniPrijenos = 1
BEGIN

INSERT INTO #tmpTemeljnica1
SELECT v.oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,sum(v.Duguje) as Duguje
,sum(v.Potra?uje) as Potra?uje
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potra?uje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,''
FROM #tmpTemeljnica as v
GROUP by
v.oznaka_analitike
,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta
,v.broj_dokumenta
,v.Datum_dokumenta
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj, v.Duguje2, v.Potra?uje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta, v.IdNo
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID,
CASE --grupiranje po predznaku...
WHEN v.duguje < 0 THEN 'M'
WHEN v.potra?uje < 0 THEN 'M'
ELSE 'P'
END

END
ELSE
BEGIN
print ''
END

INSERT INTO HeRPuSPrijenos_Temeljnica
SELECT
v.oznaka_analitike,v.Analiticki_konto
,v.Konto
,v.vrsta_dokumenta,v.broj_dokumenta,v.Datum_dokumenta
,v.Duguje,v.Potra?uje
,v.Opis
,v.Vrsta_Temeljnice, v.Broj_Temeljnice, v.Datum_temeljnice
,v.Tecaj,v.Duguje2,v.Potra?uje2
,v.Protukonto, v.Protuanalitika, v.ProtuAnliticki_konto
,v.Zatvoreno, v.Mjesec, v.Godina, v.Broj_dnevnika, v.Saldo
,v.Zatvoren_dnevnik, v.Valuta,v.rbr
,v.PodOznaka_analitike, v.Podanaliticki_konto
,v.Broj_izvornog_dokumenta, v.Vrsta_dokumentaIzvorni
,v.Broj_dokumentaIzvorni, v.Datum_dokumentaIzvorni
,v.DugujeDEV, v.PotrazujeDEV, v.NovcanaJedinica
,v.ZatvaraDokument
,v.Prijenos_ID, v.Prijenos_DokID
,v.Duguje2DEV, v.Potrazuje2DEV, v.ZatvorenoDEV
,v.Racunalo, v.Korisnik, v.DatumAzuriranja
,v.Prijenos_Dok_Dogadjaj, v.Prijenos_dok_zatvara
,v.Red_ID, v.Tem_ID--,v.RVrDok --, v.RK_ID
FROM #tmpTemeljnica1 as V

IF @.@.ERROR <> 0
BEGIN
BEGIN TRANSACTION T1
INSERT INTO HeRPuSPrijenos_Greske
(Knjizenje_ID,Modul,Stavka_ID,Stavka_Rbr,Broj_Greske,Opis_Greske1,Opis_Greske2,Opis_Greske3,VrijemeUpisa,Korisnik,Racunalo)
VALUES
(0,'Robni prijenos',0,0,205,'Greska prilikom prebacivanja podataka u HeRPuSPrijenos_Temeljnica','','',getdate(),'server','server')
COMMIT TRANSACTION T1
SET @.Greska=1
END

END

-- DROP TABLE #tmpDokumenti --necemo dropat jel ce server dropat nakon zavrsetka procedure
-- DROP TABLE #tmpTemeljnica
-- DROP TABLE #tmpTemeljnica1

IF @.Greska=0
BEGIN
set @.SQL_Update='update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=1, Temeljnica_Greska=0 Where ID IN (' + @.vbcrlf
set @.SQL_Update=@.SQL_Update + @.IDs + ') AND Dok_AK=''' + @.VrDok + '''' + @.vbcrlf
EXEC (@.SQL_Update)

UPDATE robne_kartice SET Knjizenje_financijsko=1 WHERE ID IN --updejta robne kartice na knjizeno
(
SELECT v.RK_ID
FROM vfk_rk as v INNER JOIN #tmpDokumenti as D
ON v.broj_dokumenta=d.dok_Broj AND V.AK_skladiste=D.AK_skladiste AND V.vrsta_dokumenta=@.VrDok
)

END
ELSE
BEGIN
set @.SQL_Update='update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=0, Temeljnica_Greska=1 Where ID IN (' + @.vbcrlf
set @.SQL_Update=@.SQL_Update + @.IDs + ') AND Dok_AK=''' + @.VrDok + '''' + @.vbcrlf

EXEC (@.SQL_Update)
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--SNIP--

update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=1, Temeljnica_Greska=0 Where
ID IN (select ID from #tmpDokumenti)

It would be great if you reported this at http://lab.msdn.microsoft.com/productfeedback. If you do, you are more likely to get the attention of the right people, and you might find out if this is a known bug, and if so, what the status is.

Thanks.

Part of SP not executing... Maybe BUG!?

Hello!

I have a strange problem in one SP. One small part of it wan't execute.

I have pasted whole procedure here and a solution to problem.

So, black on red part of code will not execute if black on blue code is in place.

But, if I supstitute black on blue with black on green, black on red part will execute!

Only thing I want to know is why is that hapening...

Regards

--SNIP--

CREATE procedure sp_FINKO_Knjizenje_Robnih
(
@.VrDok varchar(8) = '', --Vrsta robnog dokumenta
@.IDs varchar(2200) = '' --IDovi iz HeRPuSPrijenos_Knjizenja
)
AS
BEGIN

set @.VrDok = rtrim(ltrim(@.VrDok))
set @.Ids = rtrim(ltrim(@.Ids))

if @.VrDok='' OR len(@.IDs) = 0
BEGIN
RAISERROR ('Potrebna vrsta dokumenta i IDovi', 16, 1)
return
END
declare @.vbCrLf char(2)
declare @.SQL_temp nvarchar(4000)

set @.vbcrlf = CHAR(13) + CHAR(10)

set @.SQL_temp= 'SET NOCOUNT ON ' + @.vbcrlf + @.SQL_temp

INSERT INTO #tmpDokumenti
exec (@.SQL_temp) --prebacujemo

SET @.Greska = 0 --ovdje pratimo jeli bilo kakvih gresaka a temeljnici

UPDATE #tmpTemeljnica SET
Broj_temeljnice =@.Tem_broj
,Tem_ID =@.Tem_ID
,Datum_temeljnice =@.Tem_datum
,Mjesec =@.Knjiz_Mjesec
,Godina =@.Knjiz_Godina
,Broj_dnevnika =@.Knjiz_Dnevnik

--idemo si napravit kopiju temp tablice(treba nam zbog grupnoh odnosno pojedinacnog prijenosa)
SELECT * INTO #tmpTemeljnica1
FROM #tmpTemeljnica WHERE 1=2

--idemo sad grupirat sve sto treba
SET @.PojedinacniPrijenos =1 --zasad ostajemo na ovom

IF @.PojedinacniPrijenos = 1
BEGIN

END
ELSE
BEGIN
print ''
END

END

-- DROP TABLE #tmpDokumenti --necemo dropat jel ce server dropat nakon zavrsetka procedure
-- DROP TABLE #tmpTemeljnica
-- DROP TABLE #tmpTemeljnica1

EXEC (@.SQL_Update)
END
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--SNIP--

update HeRPuSPrijenos_Knjizenja set Temeljnica_Nastala=1, Temeljnica_Greska=0 Where
ID IN (select ID from #tmpDokumenti)

Thanks.

Part

Hi folks,
I posted this somewhere else here, excuse me,,,
I have the following challenge
When I create a stored procedure the selects data from the portioned view,
the execution plan shows that SQL server scans all base tables, while the
select statement after replacing the variable with an actual value scans onl
y
the required table.
Here is a full script:
Execute the commented out statements at the end of the script with show
graphical execution plan option and see the difference.
SET NOCOUNT ON
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[EMPLOYEES]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_1]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_2]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[Employees_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Employees_3]
GO
CREATE TABLE [Employees_1] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_1] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_1] CHECK ([FirstChar] = 1)
) ON [PRIMARY]
CREATE TABLE [Employees_2] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_2] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_2] CHECK ([FirstChar] = 2)
) ON [PRIMARY]
CREATE TABLE [Employees_3] (
[EmpID] [int] NOT NULL ,
[EmpName] [char] (5) NULL ,
[FirstChar] [int] NOT NULL ,
CONSTRAINT [PK_Employees_3] PRIMARY KEY CLUSTERED
(
[EmpID],
[FirstChar]
) ON [PRIMARY] ,
CONSTRAINT [CK_Employees_3] CHECK ([FirstChar] = 3)
) ON [PRIMARY]
GO
--
CREATE VIEW EMPLOYEES
AS
SELECT *
FROM EMPLOYEES_1
UNION ALL
SELECT *
FROM EMPLOYEES_2
UNION ALL
SELECT *
FROM EMPLOYEES_3
GO
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (1,'aaaaa',1)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (2,'aaaab',1)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (3,'baaaa',2)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (4,'baaab',2)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (5,'caaaa',3)
INSERT INTO EMPLOYEES
(EmpID, EmpName, FirstChar)
VALUES (6,'caaab',3)
go
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GetEmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEmp]
GO
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[GetEmp2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetEmp2]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create proc GetEmp
@.firstchar int
as
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = @.firstchar
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc GetEmp2
@.firstchar int
as
declare @.sql varchar(8000)
set @.sql ='
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = ' + convert(varchar,@.firstchar)
exec (@.sql)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
/*
select * from employees
where (EMPNAME IS NOT NULL)
and firstchar = 2
exec GetEmp 2
exec GetEmp2 2
*/Use STATISTICS IO and you will see that SQL Server will only access one of t
he table for the GetEmp
procedure. But the plan doesn't show that because the same plan should work
for whatever value you
send in the parameter. So elimination of the other views are deferred until
run-time.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"RATA" <RATA@.discussions.microsoft.com> wrote in message
news:B68CA9C0-02B9-4A55-8904-B7DC3EBE727C@.microsoft.com...
> Hi folks,
> I posted this somewhere else here, excuse me,,,
> I have the following challenge
> When I create a stored procedure the selects data from the portioned view,
> the execution plan shows that SQL server scans all base tables, while the
> select statement after replacing the variable with an actual value scans o
nly
> the required table.
> Here is a full script:
> Execute the commented out statements at the end of the script with show
> graphical execution plan option and see the difference.
>
> SET NOCOUNT ON
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[EMPLOYEES]') and OBJECTPROPERTY(id, N'IsView') = 1)
> drop view [dbo].[EMPLOYEES]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_1]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_2]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_2]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[Employees_3]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
> drop table [dbo].[Employees_3]
> GO
> CREATE TABLE [Employees_1] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_1] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_1] CHECK ([FirstChar] = 1)
> ) ON [PRIMARY]
> CREATE TABLE [Employees_2] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_2] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_2] CHECK ([FirstChar] = 2)
> ) ON [PRIMARY]
> CREATE TABLE [Employees_3] (
> [EmpID] [int] NOT NULL ,
> [EmpName] [char] (5) NULL ,
> [FirstChar] [int] NOT NULL ,
> CONSTRAINT [PK_Employees_3] PRIMARY KEY CLUSTERED
> (
> [EmpID],
> [FirstChar]
> ) ON [PRIMARY] ,
> CONSTRAINT [CK_Employees_3] CHECK ([FirstChar] = 3)
> ) ON [PRIMARY]
> GO
> --
> CREATE VIEW EMPLOYEES
> AS
> SELECT *
> FROM EMPLOYEES_1
> UNION ALL
> SELECT *
> FROM EMPLOYEES_2
> UNION ALL
> SELECT *
> FROM EMPLOYEES_3
> GO
>
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (1,'aaaaa',1)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (2,'aaaab',1)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (3,'baaaa',2)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (4,'baaab',2)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (5,'caaaa',3)
> INSERT INTO EMPLOYEES
> (EmpID, EmpName, FirstChar)
> VALUES (6,'caaab',3)
> go
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[GetEmp]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[GetEmp]
> GO
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[GetEmp2]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
> drop procedure [dbo].[GetEmp2]
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> create proc GetEmp
> @.firstchar int
> as
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = @.firstchar
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE proc GetEmp2
> @.firstchar int
> as
> declare @.sql varchar(8000)
> set @.sql ='
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = ' + convert(varchar,@.firstchar)
> exec (@.sql)
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> /*
> select * from employees
> where (EMPNAME IS NOT NULL)
> and firstchar = 2
> exec GetEmp 2
> exec GetEmp2 2
> */
>|||Karaszi,
the SP takes time much more than the select stmt, this is why i analyzed the
execution plan.
"Tibor Karaszi" wrote:

> Use STATISTICS IO and you will see that SQL Server will only access one of
the table for the GetEmp
> procedure. But the plan doesn't show that because the same plan should wor
k for whatever value you
> send in the parameter. So elimination of the other views are deferred unti
l run-time.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "RATA" <RATA@.discussions.microsoft.com> wrote in message
> news:B68CA9C0-02B9-4A55-8904-B7DC3EBE727C@.microsoft.com...
>
>|||What does statistics IO say when you execute it? I assume that you have more
data in your tables
than the test script?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"RATA" <RATA@.discussions.microsoft.com> wrote in message
news:CD107B8F-9B5B-4788-8FC1-5E25B321292D@.microsoft.com...
> Karaszi,
> the SP takes time much more than the select stmt, this is why i analyzed t
he
> execution plan.
> "Tibor Karaszi" wrote:
>|||I am sorry; there was a bug in my SP that was enforcing the engine to scan
all queries. My issue is closed now.
This posting was useful; you don’t have to rely on the graphical execution
plan always, some time you have to examine other thinks like the SET
STATISTICS IO.
Thanks all
"Tibor Karaszi" wrote:

> What does statistics IO say when you execute it? I assume that you have mo
re data in your tables
> than the test script?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "RATA" <RATA@.discussions.microsoft.com> wrote in message
> news:CD107B8F-9B5B-4788-8FC1-5E25B321292D@.microsoft.com...
>

Wednesday, March 28, 2012

Parsing values from sp_spaceused stored proc.

I am using sp_spaceused stored procedure to get the database_size result.
The result comes back as a string such as "512 KB" or "100 MB". I really
need a number value representation of the size. I though about parsing the
string, but I was not sure if there were any other results of the string
that I may not account for. Is there any standard way to parse this string,
or should I just assume that the format of the string can either have KB or
MB at the end and parse it based on that assumption?
--
Ken Varn
Senior Software Engineer
Diebold Inc.
EmailID = varnk
Domain = Diebold.com
--You can look at the contents of the stored proc via the Enterprise Manager
or
via
use master
go
sp_Helptext sp_spaceused
I don't see any values represented other than KB and MB
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken
You can create a table and store an output from sp_spaceused there.
If I understood you need the number only. So see if this helps you.
CREATE FUNCTION dbo.CleanChars
(@.str VARCHAR(8000), @.validchars VARCHAR(8000))
RETURNS VARCHAR(8000)
BEGIN
WHILE PATINDEX('%[^' + @.validchars + ']%',@.str) > 0
SET @.str=REPLACE(@.str, SUBSTRING(@.str ,PATINDEX('%[^' + @.validchars +
']%',@.str), 1) ,'')
RETURN @.str
END
GO
CREATE TABLE sometable
(namestr VARCHAR(20) PRIMARY KEY)
INSERT INTO sometable VALUES ('AB-C123')
INSERT INTO sometable VALUES ('A,B,C')
SELECT namestr,
dbo.CleanChars(namestr,'A-Z 0-9')
FROM sometable
"Ken Varn" <nospam> wrote in message
news:eXe6n$6WFHA.1468@.tk2msftngp13.phx.gbl...
> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing
the
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this
string,
> or should I just assume that the format of the string can either have KB
or
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>|||Ken,
To be sure, see the sp code from master database.
exec master..sp_helptext sp_spaceused
go
AMB
"Ken Varn" wrote:

> I am using sp_spaceused stored procedure to get the database_size result.
> The result comes back as a string such as "512 KB" or "100 MB". I really
> need a number value representation of the size. I though about parsing th
e
> string, but I was not sure if there were any other results of the string
> that I may not account for. Is there any standard way to parse this strin
g,
> or should I just assume that the format of the string can either have KB o
r
> MB at the end and parse it based on that assumption?
> --
> --
> Ken Varn
> Senior Software Engineer
> Diebold Inc.
> EmailID = varnk
> Domain = Diebold.com
> --
>
>

Parsing Text

Dear All,
I know how to parse this in ACCESS but not clear how to do this through
stored procedure. I Googled with different search criteria but didn't get an
y
useful hit. Any one have something handy?
I would like to parse:
"strFullName" listed as Habibullah, Mohammad I to three fields as:
strLastName: Habibullah
strFirstName: Mohammad
strMiddleName: I
Or another example, I would like "strFullName" listed as Huang, Chong Xi to
parse as:
strLastName: Huang
strFirstName: Chong
strMiddleName: Xi
Assuming that table name is User and field name is strFullName.
Thanks.
Habibullah.Habibullah,
You can use the T-SQL string functions to achieve your goal. See String
Functions in the SQL BOL. (esp. CHARINDEX and SUBSTRING). Ideally you'd
want to perform this string manipulation at the middle-tier or on the
client.
HTH
Jerry
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.|||Hi
One way to do it would be to use
SELECT
LEFT(strFullName,CHARINDEX(',',strFullNa
me)-1) AS strLastname,
LTRIM(SUBSTRING(strFullName,CHARINDEX(',
',strFullName)+1,LEN(strFullName)-CH
ARINDEX('
',REVERSE(strFullName))-CHARINDEX(',',strFullName))) AS strFirstname,
RIGHT(strFullName,CHARINDEX(' ',REVERSE(strFullName))-1) AS strMiddlename
FROM User
But this depends on how consistent the data is.
John
"Habibullah" <Habibullah@.discussions.microsoft.com> wrote in message
news:18A25EB1-FE23-400B-82B1-3ED108B1B8DD@.microsoft.com...
> Dear All,
> I know how to parse this in ACCESS but not clear how to do this through
> stored procedure. I Googled with different search criteria but didn't get
> any
> useful hit. Any one have something handy?
> I would like to parse:
> "strFullName" listed as Habibullah, Mohammad I to three fields as:
> strLastName: Habibullah
> strFirstName: Mohammad
> strMiddleName: I
>
> Or another example, I would like "strFullName" listed as Huang, Chong Xi
> to
> parse as:
> strLastName: Huang
> strFirstName: Chong
> strMiddleName: Xi
> Assuming that table name is User and field name is strFullName.
> Thanks.
> Habibullah.

Parse values from delimited string

Hi. I have a string like this which will be passed into a SQL Server 2000 stored procedure,

[15438|39][21347|96][24198|23]....

I need to take the values in the above string and insert them into a temp table so they will look like this. How can a delimited string be parsed into this? Thanks.

Exam ID Branch Number 15438 39 21347 96 24198 23

Look at the use of Jens' Split function, available here.

Seems like you need to split on the brackets, ][

and then again on the pipe, |

Split Function (Jens Suessmeyer)
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=419984&SiteID=17
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=326300&SiteID=1

|||The Split function returns a table. How would I call the function if it needs to split the string twice? An example is very much appreciated. Also, I'm thinking of passing the data this way to make it even simpler,

|23415^33|23451^23|....
|||

I recommand to use the XML input rather than the delimted string. You already have a structured string. Just replace the string as follow as and utilize the OPENXML; Since your schema is simple it will be simple and faster. But String manipulation will hit your performance badly(bcs you have to do split of split of split).

Sample with your input...

Code Snippet

Declare @.Data as Varchar(100);

Declare @.XML as Varchar(8000);

Set @.Data = '[15438|39][21347|96][24198|23]'

Set @.XML = Replace(Replace(Replace(@.Data,'[','<row><col1>'),'|','</col1><col2>'),']','</col2></row>')

select @.XML = '<Data>' + @.XML + '</Data>'

Declare @.iDoc as Int;

Exec sp_xml_preparedocument @.iDoc OUTPUT, @.XML

Select * From OpenXML(@.iDoc, 'Data/row', 2) With (col1 int, col2 int)

Exec sp_xml_removedocument @.iDoc

You can pass the input from your server as follow as. (Advantage: You can pass text datatype from your UI to database, but delimted values wont allow more than 8000 chars)

Code Snippet

<Root>

<row>

</row>

<row>

</row>

<row>

</row>

</Root>

|||Since you can alter the input string format, I suggest that you use comma delimited, and follow Mani's suggestion about using XML.|||

Mani,

You're quite right. String manipulation is not good with T-SQL. (Unfortunately, shredding xml isn't so hot either...)

But in this case, it may be the lesser of the evils...

|||

Yes Arnie. Since the schema is simple OPENXML perform well.

In future if they want to migrate to SQL Server 2005, they can utilize the XQuery featuer. Where we can avoid the preparedocument.

|||

As a quick aside, did you verify the code you posted for the OP?

It didn't run for me...

|||Yes. All tags are got supressed. Corrected Now. Thank you.

|||You should fix the client or application sending the data to send it in a different format (xml, fixed length blob) or call the SP multiple times. You can also dump the rows into a temporary table and process the rows in the SP. Doing string manipulations is slow and kludgy.

Parse SQL statement for list of tables

Hello,
I would like to make a procedure of sorts that accepts as input a full
sql statment and then is able to return a list (or print) of only the tables
referenced in the sql statement. Is this kind of code available?
Thanks.
Bentweak this
-- Create our Pivot table ** do this only once-- populate it with 1000 rows
CREATE TABLE NumberPivot (NumberID INT PRIMARY KEY)
DECLARE @.intLoopCounter INT
SELECT @.intLoopCounter =0
WHILE @.intLoopCounter <=999 BEGIN
INSERT INTO NumberPivot
VALUES (@.intLoopCounter)
SELECT @.intLoopCounter = @.intLoopCounter +1
END
GO
Create table #tempTables (SplitString varchar(50))
DECLARE @.chvGroupNumbers VARCHAR(1000)
SELECT @.chvGroupNumbers ='select * from authors join publishers on bla bla
bla...'
insert into #tempTables
SELECT SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID + 1,
CHARINDEX(' ', ' ' + @.chvGroupNumbers + ' ', NumberID + 1) - NumberID -1)AS
Value
FROM NumberPivot
WHERE NumberID <= LEN(' ' + @.chvGroupNumbers + ' ') - 1
AND SUBSTRING(' ' + @.chvGroupNumbers + ' ', NumberID, 1) = ' '
GO
select * from #tempTables where Splitstring in (SELECT table_name FROM
INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE ='BASE TABLE')
Use Pubs for this example
http://sqlservercode.blogspot.com/
"Ben" wrote:

> Hello,
> I would like to make a procedure of sorts that accepts as input a full
> sql statment and then is able to return a list (or print) of only the tabl
es
> referenced in the sql statement. Is this kind of code available?
> Thanks.
> Ben

parse query

i am trying to write a stored procedure which parses the string query passed
as input and returns whether it is a valid statement or not
was trying to use "SET PARSEONLY ON" without any luck
thanks
red"Parseonly" does not parse for dynamic query. This is by design. Basically,
'parseonly' only parses for syntax and dynamic query is parsed at runtime.
-- this would parse fine
-- because @.sql is a valid variable
-- and exec(@.sql) syntactically correct
-- though this would err at runtime
set parseonly on
go
declare @.sql sysname
set @.sql='aflasfasfaslfsaf'
exec(@.sql)
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>i am trying to write a stored procedure which parses the string query
>passed
> as input and returns whether it is a valid statement or not
> was trying to use "SET PARSEONLY ON" without any luck
> thanks
> red|||is there any other way that i can make it to work
srinivas
"oj" wrote:
> "Parseonly" does not parse for dynamic query. This is by design. Basically,
> 'parseonly' only parses for syntax and dynamic query is parsed at runtime.
> -- this would parse fine
> -- because @.sql is a valid variable
> -- and exec(@.sql) syntactically correct
> -- though this would err at runtime
> set parseonly on
> go
> declare @.sql sysname
> set @.sql='aflasfasfaslfsaf'
> exec(@.sql)
> --
> -oj
>
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >i am trying to write a stored procedure which parses the string query
> >passed
> > as input and returns whether it is a valid statement or not
> >
> > was trying to use "SET PARSEONLY ON" without any luck
> >
> > thanks
> > red
>
>|||No.
--
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||Hi
You could exec it prepend with SET PARSEONLY ON?
DECLARE @.sql varchar(8000)
DECLARE @.errval int
SET @.sql = 'SELECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SELECT * FROM '
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
SET @.sql = 'SEECT * FROM PUBS..Authors'
EXEC ('SET PARSEONLY ON ' + @.SQL)
SET @.errval = @.@.ERROR
IF @.errval = 0
EXEC (@.SQL)
ELSE
PRINT 'ERROR IN STATEMENT:' + @.SQL
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> is there any other way that i can make it to work
> srinivas
> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>|||hi John
what if the table name doesnt exist in the database... say
"select * from authrs" instead of "select * from authors"....
your code still executes it( shouldnt the parse take care of that
too....please correct me if i am wrong)
thanks
red
"John Bell" wrote:
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
> > is there any other way that i can make it to work
> >
> > srinivas
> >
> > "oj" wrote:
> >
> >> "Parseonly" does not parse for dynamic query. This is by design.
> >> Basically,
> >> 'parseonly' only parses for syntax and dynamic query is parsed at
> >> runtime.
> >>
> >> -- this would parse fine
> >> -- because @.sql is a valid variable
> >> -- and exec(@.sql) syntactically correct
> >> -- though this would err at runtime
> >> set parseonly on
> >> go
> >> declare @.sql sysname
> >> set @.sql='aflasfasfaslfsaf'
> >> exec(@.sql)
> >>
> >> --
> >> -oj
> >>
> >>
> >>
> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
> >> >i am trying to write a stored procedure which parses the string query
> >> >passed
> >> > as input and returns whether it is a valid statement or not
> >> >
> >> > was trying to use "SET PARSEONLY ON" without any luck
> >> >
> >> > thanks
> >> > red
> >>
> >>
> >>
>
>|||Ah yes. If 'parseonly' is part of the statement. The entire string will get
parsed at runtime (i.e. exec()).
--
-oj
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:%23E1YRyrQFHA.2948@.TK2MSFTNGP14.phx.gbl...
> Hi
> You could exec it prepend with SET PARSEONLY ON?
> DECLARE @.sql varchar(8000)
> DECLARE @.errval int
> SET @.sql = 'SELECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SELECT * FROM '
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> SET @.sql = 'SEECT * FROM PUBS..Authors'
> EXEC ('SET PARSEONLY ON ' + @.SQL)
> SET @.errval = @.@.ERROR
> IF @.errval = 0
> EXEC (@.SQL)
> ELSE
> PRINT 'ERROR IN STATEMENT:' + @.SQL
> John
> "red60man" <red60man@.discussions.microsoft.com> wrote in message
> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> is there any other way that i can make it to work
>> srinivas
>> "oj" wrote:
>> "Parseonly" does not parse for dynamic query. This is by design.
>> Basically,
>> 'parseonly' only parses for syntax and dynamic query is parsed at
>> runtime.
>> -- this would parse fine
>> -- because @.sql is a valid variable
>> -- and exec(@.sql) syntactically correct
>> -- though this would err at runtime
>> set parseonly on
>> go
>> declare @.sql sysname
>> set @.sql='aflasfasfaslfsaf'
>> exec(@.sql)
>> --
>> -oj
>>
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >i am trying to write a stored procedure which parses the string query
>> >passed
>> > as input and returns whether it is a valid statement or not
>> >
>> > was trying to use "SET PARSEONLY ON" without any luck
>> >
>> > thanks
>> > red
>>
>|||well, parseonly only parses for sql well-formed/syntax. It does not check
for the object's existence.
DECLARE @.sql sysname
SET @.sql = 'SELECT blah '
EXEC ('SET PARSEONLY ON ' + @.SQL)
PRINT(@.@.ERROR)
-oj
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>|||Hi
Even with dynamic SQL your tables existance should not be in doubt,
otherwise you are almost certainly open to SQL injection
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
Also check out:
http://www.sommarskog.se/dynamic_sql.html
http://www.sommarskog.se/dyn-search.html
John
"red60man" <red60man@.discussions.microsoft.com> wrote in message
news:BBD5F63C-9276-49E6-802D-F2A3E93B7791@.microsoft.com...
> hi John
> what if the table name doesnt exist in the database... say
> "select * from authrs" instead of "select * from authors"....
> your code still executes it( shouldnt the parse take care of that
> too....please correct me if i am wrong)
> thanks
> red
> "John Bell" wrote:
>> Hi
>> You could exec it prepend with SET PARSEONLY ON?
>> DECLARE @.sql varchar(8000)
>> DECLARE @.errval int
>> SET @.sql = 'SELECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SELECT * FROM '
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> SET @.sql = 'SEECT * FROM PUBS..Authors'
>> EXEC ('SET PARSEONLY ON ' + @.SQL)
>> SET @.errval = @.@.ERROR
>> IF @.errval = 0
>> EXEC (@.SQL)
>> ELSE
>> PRINT 'ERROR IN STATEMENT:' + @.SQL
>> John
>> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> news:66643011-8FA6-484F-813C-F6EE9CD537DF@.microsoft.com...
>> > is there any other way that i can make it to work
>> >
>> > srinivas
>> >
>> > "oj" wrote:
>> >
>> >> "Parseonly" does not parse for dynamic query. This is by design.
>> >> Basically,
>> >> 'parseonly' only parses for syntax and dynamic query is parsed at
>> >> runtime.
>> >>
>> >> -- this would parse fine
>> >> -- because @.sql is a valid variable
>> >> -- and exec(@.sql) syntactically correct
>> >> -- though this would err at runtime
>> >> set parseonly on
>> >> go
>> >> declare @.sql sysname
>> >> set @.sql='aflasfasfaslfsaf'
>> >> exec(@.sql)
>> >>
>> >> --
>> >> -oj
>> >>
>> >>
>> >>
>> >> "red60man" <red60man@.discussions.microsoft.com> wrote in message
>> >> news:876C74F5-4FB1-4EA6-89D6-E2D90E9FAE03@.microsoft.com...
>> >> >i am trying to write a stored procedure which parses the string query
>> >> >passed
>> >> > as input and returns whether it is a valid statement or not
>> >> >
>> >> > was trying to use "SET PARSEONLY ON" without any luck
>> >> >
>> >> > thanks
>> >> > red
>> >>
>> >>
>> >>
>>

parse query

Wednesday, March 21, 2012

parse query

Tuesday, March 20, 2012

Parent and grand parent and great grand parent etc.

Hi,
I have a table with id, parent-id.
I want to know how to write a stored procedure which returns the ids of
parent, grand-parent, great-grand-parent etc.
So, if the structure is like this
id Parent-id
Tree Control ASP.NET Controls
ASP.NET Controls ASP.NET
ASP.NET .NET
.NET VS
VS MS
Office MS
A parent can have more then one child
If given 'Tree Control' This stored procedure returns a record set like
following:
Tree Control
ASP.NET Controls
ASP.NET
.NET
VS
MS
ThanksOthers may have a better solution for this, but I worked out the
following for a previous question:
http://groups.google.com/group/SQL-...94663b43a00f67d
Might give you some insight.
HTH,
Stu|||Get a copy of TREES & HIERATRCHIES IN SQL for better ways to do this.|||I am looking for a simple query (at least for you guru guys out there).
For a given node, it should return the list of all the parents of it's
parent/grand parent. Usually people have examples for children - I am
not looking for children, I am looking for parent.
thanks|||Have a look at
http://toponewithties.blogspot.com/...er.htm
l
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sehboo" <masoodadnan@.hotmail.com> wrote in message
news:1123511813.304233.160130@.o13g2000cwo.googlegroups.com...
>I am looking for a simple query (at least for you guru guys out there).
>
> For a given node, it should return the list of all the parents of it's
> parent/grand parent. Usually people have examples for children - I am
> not looking for children, I am looking for parent.
> thanks
>|||Sorry, anything else? I don't want to get into prime number.
I am just looking for a simple query/stored procedure.
thanks|||OK, What about this one.
http://www.windowsitpro.com/SQLServ...es.blogspot.com
"Sehboo" <masoodadnan@.hotmail.com> wrote in message
news:1123522959.424109.230440@.g49g2000cwa.googlegroups.com...
> Sorry, anything else? I don't want to get into prime number.
> I am just looking for a simple query/stored procedure.
> thanks
>|||Look at this example:
http://milambda.blogspot.com/2005/0...or-monkeys.html
Good design is crucial in these cases - you must never allow for circular
references.
ML

Monday, March 12, 2012

Parameters to SP ?

How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.You cannot pass a table variable as an input to the SP. Not sure about your logic but if it is unavoidable, one way is to populate a temp table and read from the SP and then delete it as soon as you exit from the called SP. This is one option. Sure there could be some better ones.

- CB

Originally posted by Decastod
How can I pass a table variable as a parameter to a stored procedure ?

eg.
create proc procname ( @.param table (col1 int ) )
as
select ....

I get the following error :
Server: Msg 156, Level 15, State 1, Procedure procname, Line 1
Incorrect syntax near the keyword 'table'.

Is there any alternate way to do this ?

Thanks.|||Thanks for your help.
Tired it in another way as you mentioned and it worked.

Parameters to reports that only show is previous parameters filled

I don't know if I can do this with Reporting Services. I have ten parameters to a stored procedure. However, each parameter only shows when the previous one is selected. For sake of this discussion, the queries from which the parameters come are completly unrelated.
Initially, only the pull-down for parameter 1 appears. When the user selects a value for parameter 1 using the initial pull-down, the pull down for parameter 2 should appear. When the user selects a value from this pull down, a pull down from parameter 3 should appear.
At any point, the user can click View Report and pass the parameters he selected and all other parameters null.
Thanks in advance for any help.
JustinYes, RS supports sequenced parameter prompting. You build the valid values
query for a parameter using a query that includes previously selected
parameter values. I believe there is a sample called 'Product Line Sales'
that illustrates this technique.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Justin30519" <Justin30519@.discussions.microsoft.com> wrote in message
news:01FC2CE7-C435-47EC-AD00-E2762510E522@.microsoft.com...
>I don't know if I can do this with Reporting Services. I have ten
>parameters to a stored procedure. However, each parameter only shows when
>the previous one is selected. For sake of this discussion, the queries from
>which the parameters come are completly unrelated.
> Initially, only the pull-down for parameter 1 appears. When the user
> selects a value for parameter 1 using the initial pull-down, the pull down
> for parameter 2 should appear. When the user selects a value from this
> pull down, a pull down from parameter 3 should appear.
> At any point, the user can click View Report and pass the parameters he
> selected and all other parameters null.
> Thanks in advance for any help.
> Justin
>|||> Yes, RS supports sequenced parameter prompting. You build the valid values
> query for a parameter using a query that includes previously selected
> parameter values. I believe there is a sample called 'Product Line Sales'
> that illustrates this technique.
>
Thank you very much for your reply. It was helpful. I can do what my boss asked now. :)
Justin

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedu
re.
Please Try To Find it.
Message posted via http://www.sqlmonster.com
You can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.c om...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com

Parameters Status in Store Procedure

Hi,
Actually we want to know the type of parameters in stored procedure. Means are they Default Parameter or Mandatory. Where this information is store? I can get ISNULLABLE Column from syscolumns table but it is only applicable to table not stored procedure.
Please Try To Find it.
--
Message posted via http://www.sqlmonster.comYou can get parameter name and data type information for stored procedure
parameters from the INFORMATION_SCHEMA.PARAMETERS view. I don't know of a
way to retrieve parameter default values, though.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"ganesh wayachal via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:f81eb353fc9e47b5a8bbd5696b15b3b8@.SQLMonster.com...
> Hi,
> Actually we want to know the type of parameters in stored procedure.
> Means are they Default Parameter or Mandatory. Where this information is
> store? I can get ISNULLABLE Column from syscolumns table but it is only
> applicable to table not stored procedure.
> Please Try To Find it.
> --
> Message posted via http://www.sqlmonster.com

Friday, March 30, 2012

Part of SP not executing... Maybe BUG!?

Part of SP not executing... Maybe BUG!?

Part

Wednesday, March 28, 2012

Parsing values from sp_spaceused stored proc.

Parsing Text

Parse values from delimited string

Parse SQL statement for list of tables

parse query

parse query

Wednesday, March 21, 2012

parse query

Tuesday, March 20, 2012

Parent and grand parent and great grand parent etc.

Monday, March 12, 2012

Parameters to SP ?

Parameters to reports that only show is previous parameters filled

Parameters Status in Store Procedure

Parameters Status in Store Procedure

Parameterized Expression

Blog Archive

About Me