November 16, 2010 at 9:58 am
I have a couple questions with the below script that I am trying to make generic from a hardcoded version.
1. Is there a way to declare an existing permanent table as a variable? In this case, temp_cdmmaster below is a table that already exists within the database. This would simply be used in making the script generic so that it could apply to any table within the database.
2. The @primarykey-2 variable is an integer in the database, but I'm unsure of how to SET it properly. In this case I cannot hard code it to a single key as multiple values will be returned. In my case I want the column "Line" as that is the column label for the @primarykey-2 variable.
3. Lastly, it does not like the group by clause with the variable in place. I receive the following error message: "Each GROUP BY expression must contain at least one column that is not an outer reference."
Any help would be greatly appreciated and I am definitely not married to the syntax already utilized!
Declare @reference varchar(50), @primarykey-2 int
SET @reference = ''
SET @primarykey-2 = ''
create table temp_raw_todelete (Reference varchar(100) not null, PrimaryKey int not null);
insert into temp_raw_todelete (Reference, PrimaryKey)
select @reference, MIN(@primarykey) from temp_cdmmaster group by @reference having count(*) > 1;
IF (SELECT Reference FROM temp_raw_todelete) is null
BEGIN
RAISERROR ('<<<<No values present in target table>>>>',10,1)
drop table temp_raw_todelete
RETURN
END
ELSE
delete from temp_cdmmaster
where @primarykey-2 in (
select a.@primarykey from temp_cdmmaster a inner join temp_raw_todelete b on a.@reference=b.@reference
where b.@primarykey <> a.primarykey)
drop table temp_raw_todelete
November 16, 2010 at 10:06 am
1. The only way you'll be able to make a generic script that can work for any table is to use dynamic SQL. Here's a good reference for that.
2. To SET your variables, convert your code to a stored procedure so that you can pass the variable values into the proc. Look up CREATE PROCEDURE in BOL.
3. It looks like you've got some work to do on your SQL. You've combining variables (in several places) where columns should exist. For this example, a more appropriate statement would look like this:
select reference, MIN(@primarykey) from temp_cdmmaster where reference = @reference group by reference having count(*) > 1;
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply