parameters in stored procedure...

Hello,
I am writing a stored procedure and noticed for a
particular stored procedure there is a pattern that I
would consider writing a template class for. In
particular the names of the tables, the name of field for
the table that I am checking for and the name of data..
( I don't know if my table design is good or not... but I
figured that if the names were all the same I should be
able to get some code reuse somewhere... )
Anyway the question I have: Is there a way that I can
capitolize on the fact that the fields have the same names
in the tables?
for example: (very rough pseudo code )
exec AddData( "City", @.Name )
exec AddData( "State", @.Name
Where "City" and "State" are table names
and @.Name is the name of the city/state that I am
interested in adding to the DB:
Here is what I am currently doing:
ie:
if exists( select City.[Index] from City where City.Name = @.CityName )
begin
select @.IndexCity = City.[Index] from City where City.Name
= @.CityName
end
else
begin
set @.IndexCity = newid()
insert into City( City.[Index], Name) values( @.IndexCity,
@.CityName )
end
if exists( select State.[Index] from State where
State.Name = @.StateName )
begin
select @.IndexState = State.[Index] from State where
State.Name = @.StateName
end
else
begin
set @.IndexState = newid()
insert into State( State.[Index], Name) values(
@.IndexState, @.StateName )
end
Thanks
MeYou would have to use dynamic SQL, which has a lot of drawbacks. See:
http://www.sommarskog.se/dynamic_sql.html
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Me" <anonymous@.discussions.microsoft.com> wrote in message
news:06c301c3c45d$2f13df60$a301280a@.phx.gbl...
> Hello,
> I am writing a stored procedure and noticed for a
> particular stored procedure there is a pattern that I
> would consider writing a template class for. In
> particular the names of the tables, the name of field for
> the table that I am checking for and the name of data..
> ( I don't know if my table design is good or not... but I
> figured that if the names were all the same I should be
> able to get some code reuse somewhere... )
> Anyway the question I have: Is there a way that I can
> capitolize on the fact that the fields have the same names
> in the tables?
> for example: (very rough pseudo code )
> exec AddData( "City", @.Name )
> exec AddData( "State", @.Name
> Where "City" and "State" are table names
> and @.Name is the name of the city/state that I am
> interested in adding to the DB:
> Here is what I am currently doing:
>
> ie:
> if exists( select City.[Index] from City where City.Name => @.CityName )
> begin
> select @.IndexCity = City.[Index] from City where City.Name
> = @.CityName
> end
> else
> begin
> set @.IndexCity = newid()
> insert into City( City.[Index], Name) values( @.IndexCity,
> @.CityName )
> end
>
> if exists( select State.[Index] from State where
> State.Name = @.StateName )
> begin
> select @.IndexState = State.[Index] from State where
> State.Name = @.StateName
> end
> else
> begin
> set @.IndexState = newid()
> insert into State( State.[Index], Name) values(
> @.IndexState, @.StateName )
> end
>
> Thanks
> Me|||Thanks
>--Original Message--
>You would have to use dynamic SQL, which has a lot of
drawbacks. See:
>http://www.sommarskog.se/dynamic_sql.html
>--
>Tibor Karaszi, SQL Server MVP
>Archive at: http://groups.google.com/groups?
oi=djq&as_ugroup=microsoft.public.sqlserver
>
>"Me" <anonymous@.discussions.microsoft.com> wrote in
message
>news:06c301c3c45d$2f13df60$a301280a@.phx.gbl...
>> Hello,
>> I am writing a stored procedure and noticed for a
>> particular stored procedure there is a pattern that I
>> would consider writing a template class for. In
>> particular the names of the tables, the name of field
for
>> the table that I am checking for and the name of data..
>> ( I don't know if my table design is good or not... but
I
>> figured that if the names were all the same I should be
>> able to get some code reuse somewhere... )
>> Anyway the question I have: Is there a way that I can
>> capitolize on the fact that the fields have the same
names
>> in the tables?
>> for example: (very rough pseudo code )
>> exec AddData( "City", @.Name )
>> exec AddData( "State", @.Name
>> Where "City" and "State" are table names
>> and @.Name is the name of the city/state that I am
>> interested in adding to the DB:
>> Here is what I am currently doing:
>>
>> ie:
>> if exists( select City.[Index] from City where
City.Name =>> @.CityName )
>> begin
>> select @.IndexCity = City.[Index] from City where
City.Name
>> = @.CityName
>> end
>> else
>> begin
>> set @.IndexCity = newid()
>> insert into City( City.[Index], Name) values(
@.IndexCity,
>> @.CityName )
>> end
>>
>> if exists( select State.[Index] from State where
>> State.Name = @.StateName )
>> begin
>> select @.IndexState = State.[Index] from State where
>> State.Name = @.StateName
>> end
>> else
>> begin
>> set @.IndexState = newid()
>> insert into State( State.[Index], Name) values(
>> @.IndexState, @.StateName )
>> end
>>
>> Thanks
>> Me
>
>.
>