Dynamic SQL

sanjivbshinde · January 9th, 2007, 07:37 AM

Hello Friends,
In Sql I have faced one problem.
I have cretaed two tables

Table1
SrNo. Allowance Prompt Fromdate Todate
1 Allowance1 HRA 12/30/2006 NULL
2 Deduction5 PF 12/30/2006 NULL

Table2
SrNo EmpId Allowance1 Allowance2 Deduction1 Deduction2 Deduction5
1 1 0.0 100 0.0 0.0 50
2 2 10.0 0.0 10.0 0.0 100.0

these are my two tables with some data.

Now I want to find value of PF for employee 2

for that i write one stoered procedure which takes employee id as parameter

create proc ss
@EmpId int
as
declare @ADType varchar(30)
declare @sql varchar(1000)

select @ADType=Allowance from Table1 where prompt='PF' and Todate is null
set @sql='select ' + @ADType + ' from Table2 where Empid= ' + Cast(@EmpId as varchar(50))
exec(@sql)
GO

this is what is my stoered procedure.
If i run it it gives me correct output
as

Deduction5
100.0

but i m unable to access this procedure from ASP.net as it contains dynamic sql.
for that i have to fill Dataset but dataset can't fill with that one column
insted it shows zero tables as well as zero columns.
But my requirement is that i have to fill that Dataset with that column.
So plze tell solution for that.

Thanks,
Sanjiv shinde.

planoie · January 9th, 2007, 11:45 AM

You should consider a redesign of your table #2. Normalization rules would state that table2 is formed improperly because you have multiple columns that represent the same kind of data (i.e. you have 2 Allowance columns and 3 deduction columns).

Instead, build the table with a column that defines the type of data in the row (allowance OR deduction) and a column that lists which of them it is. This also permits expanding the data without changing the table (i.e. added a 3rd allowance or a 4th deduction) because you just create another key value. Here's what it could look like:

Code:

Table2
SrNo EmpId RowType TypeNum Amount
1     1     1       1       0.0
1     1     1       2       100
1     1     -1      1       0.0
1     1     -1      2       0.0
1     1     -1      5       50
2     2     1       1       10.0
2     2     1       2       0.0
2     2     -1      1       10.0
2     2     -1      2       0.0
2     2     -1      5       100.0

I chose 1 and -1 because you could use that directly in SQL to perform math against the value in the "Amount" column. "Amount * RowType" gives you a value to add to whatever you need to add it to. The -1 results in a "deduction" (such as -100).

The additional columns "RowType" and "TypeNum" added to "SrNo" and "EmpId" result in a 4 column key to the table. Then your stored procedure can always reference the same column regardless of whether you want an Allowance (RowType=1) or Deduction (RowType=-1).

This isn't a complete solution to your problem, but I hope it provides a suggestion towards normalization of the data that will lead to an easier stored procedure and less pain later on.

-Peter