SP wont load - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SP wont load

Author

Topic

ddasilva99
Starting Member

26 Posts

Posted - 2006-10-12 : 23:50:47

Hi Team,

Ive written the Stored Procedure below but it wont run. I get the following error:

Any help would be greatly appreciated.

ALTER PROCEDURE d6661a03.AuthenticateUser
@tblName VARCHAR(50),
@name VARCHAR(50),
@email VARCHAR(50),
@comments VARCHAR(50),
@enabled varchar(1)
AS
begin
declare @SQL VARCHAR(1000)
set @SQL = 'INSERT INTO ' + @tblName + '(name, email, comments, enabled) ' +
'VALUES(' + @name + ',' + @email + ',' + @comments + ',' + cast(@enabled as varchar(50)) + ')'
exec (@SQL)
end

OUTPUT from VS2005
Running [d6661a03].[AuthenticateUser] ( @tblName = d6661a03.AdministratorInfo, @name = derek, @email = tester, @comments = testing, @enabled = 1 ).

The name 'derek' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
No rows affected.
(0 row(s) returned)
@RETURN_VALUE = 0
Finished running [d6661a03].[AuthenticateUser].

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2006-10-13 : 00:09:52

print the tsql you built to verify it is correct

i prefer to use select instead of values...
makes placing the quotes much easier

also, why do you need to cast @enabled? why not resize it in the parameter declaration as 50?

HTH

--------------------
keeping it simple...

ddasilva99
Starting Member

26 Posts

Posted - 2006-10-13 : 00:13:11

Hi Jen,

Thank you for your response. How do I print the TSQL and how do I make sure if select?
@enabled is a bit field in my table used to determine if the account is active (1=active).

Thanks again,
D

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 00:52:14

[code]ALTER PROCEDURE d6661a03.AuthenticateUser
@tblName VARCHAR(50),
@name VARCHAR(50),
@email VARCHAR(50),
@comments VARCHAR(50),
@enabled varchar(1)
AS
begin
declare @SQL VARCHAR(1000)
set @SQL = 'INSERT INTO ' + @tblName + '(name, email, comments, enabled) ' +
'VALUES(''' + @name + ''',''' + @email + ''',''' + @comments + ''',''' + cast(@enabled as varchar(50)) + ''')'
exec (@SQL)
end[/code]

Peter Larsson
Helsingborg, Sweden

ddasilva99
Starting Member

26 Posts

Posted - 2006-10-13 : 01:00:34

Peter,

It worked- thank you for your help. Ill keep the "'" in mind next time. Is there a way to clean up the code so that it isnt so confusing (with all of the single quotes)?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 01:10:46

Yes. Do not use dynamic SQL.

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-10-13 : 08:35:48

Why do you want to pass table name as object?

Madhivanan

Failing to plan is Planning to fail

ddasilva99
Starting Member

26 Posts

Posted - 2006-10-13 : 10:43:29

Hi Madhivanan,

What I want to do is the following...
If I am creating a teacher ID, then I want it to go into the teacher table,
if I am creating a student, I want it to go into the student table,
If I want to create an admin, I want it to go into the admin table

By creating a Dynam SP- I can use one SP to do all of the above rather than duplicating code for one SP to add a student, and teacher and admin...

What are your thoughts on my logic- is it flawed?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-13 : 10:48:34

If all tables has the same structure, there is no need to.

If @Param1 = 'Teacher'
insert teacher (col1, col2, ...) values (a, b, c, ...)

If @Param1 = 'Student'
insert teacher (col1, col2, ...) values (a, b, c, ...)

Above example is one way to go. But since all tables has same structure, why don't you make a new table with the same columns, and add a new column called 'objecttype' or something, denoting what kind of information that row holds?

Peter Larsson
Helsingborg, Sweden

ddasilva99
Starting Member

26 Posts

Posted - 2006-10-13 : 10:57:25

First off- thank you for your suggestions. I am a 3rd year student at a local college creating a "learning management system" One of my members stated we should have three seperate tables so that searching will be faster... is that true?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources