SET NOCOUNT ON

Declare @sql nvarchar(2000)
Declare @tablename nvarchar(200)

Set @Tablename = 'authors'

Create Table #temp(
	test char(4)
)

Set @sql = 'Insert into #temp SELECT ''true'' FROM ' + @TableName

Exec(@sql)

IF EXISTS(
	Select * from #temp
)
print 'hi'

drop table #temp

SET NOCOUNT OFF

quote:

---

Don't use SELECT 'TRUE' with an exists clause. If you use exists always use SELECT *
-Dutch

---

quote:

---

I like Lars' better (sorry merk...) because it does not incur the overhead of the temp table creation.

---

I agree actually. When I posted that solution I didn't like it too much, but couldn't think of a better way off the top of my head (was designing a workflow system at the time....you know how it is )

Damian

CREATE TABLE MyTable ( 
    Something INT, 
    Country varchar(30) NOT NULL REFERENCES World(Country) ) 
CREATE TABLE World (
    Country varchar(30) not null primary key )

quote:

---

am I still a normalisation-dunce?

---

If you are not sure, then you probably are ..

OK, I think I am understanding your problem a bit more ... fine. In fact, your desire to get rid of these seemingly almost redundant stored procedures is somewhat commendable.

Why no add a param to proc for value type?

create procedure usp_addvalue
    @newvalue varchar(50),
    @valuetype char(1),
    @id int output 
as
set nocount on

insert into tbltitles(title)
select
    @newvalue
where
    not exists (
        select 1
        from
            tbltitles
        where
            title = @newvalue) and
     @valuetype = 't'

select @id = 
    case when @@rowcount = 0 then -1
         else @@identity
    end

insert into tblCompanytypes(companyType)
select
    @newvalue
where
    not exists (
        select 1
        from
            tblCompanytypes
        where
            companytype = @newvalue) and
    @valuetype = 'c'

select @id = 
    case when (@@rowcount = 0 and @id is null) then -1
         else @@identity
    end

-- ect, ect ...

go

Jay White
{0}

quote:

---

Now we're getting somewhere! I guess explaining clearly what I wanted has helped...

---

Yes, it usually does

quote:

---

so that I only need to set one parameter (a table name) in the presentation layer control that displays info from the lookup table, and the code automatically adjusts everything else.

---

Sounds like a good plan. I would write separate stored procs for your data access, then use the function in your business or presentation layer to pass the table or object name dynamically.


Damian

	CREATE PROCEDURE sp_AddLookup
     	@TableName 		varchar(255),
     	@ColumnName 		varchar(255),
     	@NewLookup  		varchar(50),
     	@NewLookupID		int OUTPUT
     AS 
     	SET NOCOUNT ON 
     
     	DECLARE @intExists INT, @strSQL VARCHAR(4000) 
     	SET @strSQL = 'SELECT @exists = count(*) FROM ' + @TableName + ' WHERE ' + @ColumnName + ' = ''' + 
			@NewLookup + ''''
          
          EXEC sp_executesql @strSQL, '@exists INT OUTPUT', @intExists OUTPUT 
     	IF @intExists > 0 
     		SET @NewLookupID = -1
          ELSE
			-- need to add code to do an INSERT here
     GO 

     CREATE PROCEDURE sp_AddTitle 
     	@NewTitle  	varchar(50),
     	@TitleID		int OUTPUT
     AS
     	IF EXISTS (SELECT 'True' FROM tblTitles WHERE Title = @NewTitle)
     		BEGIN
     			--This means it exists, return -1 as the identity of the "new" record  
                  	SET @TitleID = -1
     		END
     	ELSE
     		BEGIN
     			--This means the record isn't in there already, let's go ahead and add it
     			INSERT INTO tblTitles(Title)
     			VALUES (@NewTitle)
     			SET @TitleID = @@IDENTITY
     		END
     GO
     
     
     	+++++++++++++++++++
     
     
     CREATE PROCEDURE sp_AddCompanyType 
     	@NewCompanyType  	varchar(50),
     	@CompanyTypeID		int OUTPUT
     AS
     	IF EXISTS (SELECT 'True' FROM tblCompanyTypes WHERE CompanyType = @NewCompanyType)
             	BEGIN
                  	--This means it exists, return -1 as the identity of the "new" record  
                  	SET @CompanyTypeID = -1
             	END
     	ELSE
     		BEGIN
     			--This means the record isn't in there already, let's go ahead and add it
     			INSERT INTO tblCompanyTypes(CompanyType)
     			VALUES (@NewCompanyType)
     			SET @CompanyTypeID = @@IDENTITY
     		END
     GO
     
     	+++++++++++++++++++
     
     
     CREATE PROCEDURE sp_AddCountry 
     	@NewCountry  	varchar(50),
     	@CountryID		int OUTPUT
     AS
     	IF EXISTS (SELECT 'True' FROM tblCountries WHERE Country = @NewCountry)
             	BEGIN
                  	--This means it exists, return -1 as the identity of the "new" record  
                  	SET @CountryID = -1
             	END
     	ELSE
     		BEGIN
     			--This means the record isn't in there already, let's go ahead and add it
     			INSERT INTO tblCountries(Country)
     			VALUES (@NewCountry)
     			SET @CountryID = @@IDENTITY
     		END
     GO

quote:

---

I have no problem passing the table name and column name from the presentation layer, through the business layer to the data layer, the problem comes when the data layer passes these parameters to the dynamic SP.

---

quote:

---

Didn't I post a working start to a generic proc? Doesn't it not use dynamic SQL? Doesn't it take a lookup value type as a param and insert into the correct lookup table? Is this thing on (as he taps the mic and get high-pitched feedback)?

---