Parameterized Expression: solution

Showing posts with label solution. 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.

Friday, March 23, 2012

Parent Child relationship column hint?

I've got a dilemma which I hope someone has a solution to.

Let's say we're building a data mining model to predict aircraft reliability. In the training table we've got a column (among many others) with a unique aircraft ID, and then a column for the type (737,747) and then a column for the series (100,200,300). I.E. A 737-800 series would be "737" and "800".

There is in essence a parent-child relationship between these 2 columns. 737's should share a common set of reliability factors, and then those factors might be further defined by the series number (for instance, the 737 might have very reliable radar except for the 500 series). The series is analogous to what model year a car is. What I want to make sure doesn't happen is for the system to correlate a 747-400 and a 737-400 because they are the same series. They are totally independent if the model number is different.

My only idea was to merge the columns and have a single value "737-100". But it would seem then that the model won't have any idea that a "737-100" and "737-200" should have a lot more in common than a "737-100" because the values will be completely different.

I was hoping to find some sort of parent-child hint in the column properties but found none.

What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.

Geof

You can still use two columns. The first is the type, the second is type+series:

TypeTypeSeries

737 737-100

737 737-200

…

747 747-100

This solution is basically an extension of your proposed one. Please let me know if this works for you.

Thanks,

|||

Of course! Thanks, I thought I was close.

It worked just fine.

Geof

Parent Child relationship column hint?

I've got a dilemma which I hope someone has a solution to.

I was hoping to find some sort of parent-child hint in the column properties but found none.

What solutions have other people tried? It sure seems that there should be an elegant solution for something like, but I'm missing it.

Geof

You can still use two columns. The first is the type, the second is type+series:

TypeTypeSeries

737 737-100

737 737-200

…

747 747-100

This solution is basically an extension of your proposed one. Please let me know if this works for you.

Thanks,

|||

Of course! Thanks, I thought I was close.

It worked just fine.

Geof

Parent Child relation ship table in SQL Express

I want to build Parent child relation . i have two aproaches .I would like to know which is the best solution ?

1)1st method:-

Parent table with parent id . Child table with child id and parent id.Foreign key relationship exists between parent and child tables with cascade delete option enabled.

Parent TAble

Id name

1 XYZ

Child table

id name parent id

1 abc 1

2 qwe 1

2)2nd method

table with id and parent ID. Top level element will have null value in table. eg

id name parent id

1 xyz

2 abc 1

3 qwe 2

4 adf 1

Retrieve data using recursive queries supported in SQL Express.

Which is the best solution to store parent child relationship?

To know which is better I think I'd have to know what you're doing with the data. Are you developing a deep family tree? A shallow workplace report-to list? How much data? How big are the operations?

Regarding your proposed solutions, your first solution separates people into two tables. That may make it hard to do a single list operation, such as a full roster including parents and children. The second solution leaves you doing recursive SQL to get a hierarchical list of everyone, say if a is parent of b who is parent of c who is parent of d. To get a full list the SQL can be annoying.

There's another possible configuration as well using a join table. You have one table of people, another of relationships. The people table is entirely simple. The relationship table is simply two foreign keys into the people table.

Table USER:

id Name

1 Bill

2 Bob

3 Jim

Table RELATIONSHIP:

parent child

1 2

3 1

Monday, February 20, 2012

Parameterized query returns one row with null values.

I am hoping someone could help me understand why this is happening and perhaps a solution.

I am using ASP.NET 2.0 with a SQL 2005 database.

In code behind, I am performing a query using a parameter as below:

sql = "SELECT field_name FROM myTable WHERE (field_name = @.P1)"

objCommand.Parameters.Add(New SqlParameter("@.P1", TextBox1.Text))

The parameter is obtained from TextBox1 which has valid input. However, the value is not in the table. The query should not return ANY results. However, I am getting one single row back with null values for each field requested in the query.

The SQL user account for this query has select, insert, and update permissions on the table. The query is simple, no joins, and the table has no null values in any fields. If I perform the exact same query using an account with select only permission on the table, I get what I was expecting, no records. Then if I go back to the previous user account with more permissioins, and I change the query to pass the paramter this way:

sql =String.Format("SELECT field_name FROM myTable WHERE (field_name = {0})", TextBox1.Text)

I also get NO records retuned using the same criteria.

What is going on here? I would prefer to use the parameterized query method with the account having elevated permissions. Is there some command object setting that can prevent the null row from returning?

Thanks!

I am not sure but see if adding the datatype helps:

objCommand.Parameters.Add(New SqlParameter("@.P1", SqlDbType.Varchar,30)).value = TextBox1.Text

|||

Thanks for the suggestion. I tried adding the data type as you suggested. It did not change the results.

I have found that if I change to a data reader, the null value is not being returned. So, now it looks to be related to the ExecuteScalar method.

|||

I also just realized that it is not a null value being returned but instead an empty value, ie "".

I can get around this easily enough in multiple ways, I am just wanting to understand why this is happening.

So far I have this narrowed down to the following:

A parameterized query, with a user account having select, insert, update permission, and using the ExecuteScalar method. This combination returns a record with an empty result when the criteria is not found in the table instead of returning no records at all.

|||

Eh?

ExecuteScalar is used to return the first column of the first row of the query. If there is no rows, the value comes back as null.

I think perhaps you are misunderstanding what ExecuteScalar is supposed to do. It doesn't return records, or recordsets, it returns a singular scalar value (One column of one row - the first of each).

For further help, please post the whole code block in question. How you initialize your connection, command objects, how you are actually executing the query, where you are storing the result of the query (And how it is defined), and what you expected the result to be, and what you actually got.

If the results are varying depending on what user is executing the query, please make sure that either you explicitly define the schema you want to use, or that there doesn't exist multiple tables with the same name under different schemas (Refer to the table as dbo.Table not just Table).

|||

Ok, my bad, stupid mistake(s) with both user permissions and also with the string.format method.

I at least have it consistenly returning the empty record.

One last question, why return null/empty instead of just nothing like a data reader?

Thank you very much for the response.

|||

Hi,

ExecuteScalar is designed to return a single value from a database command and the proper representation of a single non-existant value is returning null. The ExecuteScalar is a non-void method and should return something!

Enjoy C#,

Mehrdad

|||

Thank you to everyone for the help and clairification on ExecuteScalar.

Friday, March 30, 2012

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

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

Friday, March 23, 2012

Parent Child relationship column hint?

Parent Child relationship column hint?

Parent Child relation ship table in SQL Express

Monday, February 20, 2012

Parameterized query returns one row with null values.

Parameterized Expression

Blog Archive

About Me