December 10, 2008 at 2:24 pm
Hi all,
I have a simple stored procedure as follow. After I create the procedure. It is successful.
then I execute
Exec pAddNewThemeScheme 4, 'Scheme3', 0, 'Scheme3'
it tells me : Command(s) completed successfully.
but when I check the data in ThemeColor. There is no data over there at all.
I am so confused. It tells me it is successful!
but I can manually insert data with statement:
Insert ThemeColor values(4, 'Scheme3', 0, 'Scheme3')
If I don't have permission to exec stored procedure I created myself. How come it tells me it is successful? what is the possible problem?
Thank you so much!!!
Betty
Create proc [dbo].[pAddNewThemeSCheme] (@themeID int, @label varchar(50), @isActive bit, @folderName varchar(50))
As
--themeID needs to be existant in the table Theme
Declare @TID int
Select @TID=themeID from dbo.Theme where themeID=@themeID
If @TID is NULL
print 'You need to create a theme before your creating theme scheme'
Else
If len(@label)<>0
if len(@label)>50
print 'lable name is too long'
Return
If len(@folderName)<>0
if len(@label) >50
print 'folder name is loo long'
Return;
--if all checks are OK
BEGIN
set @folderName=@folderName+'/'
Insert ThemeColor values(@themeID, @label, @isActive, @folderName)
COMMIT TRANSACTION
END
GO
December 10, 2008 at 2:40 pm
PRINT statements does't trigger an error. They are just informational messages.
CREATE PROCEDURE dbo.pAddNewThemeSCheme
(
@themeID int,
@label varchar(50),
@isActive bit,
@folderName varchar(50)
)
AS
SET NOCOUNT ON
IF NOT EXISTS (SELECT * FROM dbo.Theme WHERE themeID = @themeID)
BEGIN
RAISERROR('You need to create a theme before your creating theme scheme.', 16, 1)
RETURN -100
END
IF @Label IS NULL OR @Label = ''
BEGIN
RAISERROR('Lable name is missing.', 16, 1)
RETURN
END
IF @folderName IS NULL OR @folderName = ''
BEGIN
RAISERROR('Folder name is missing.', 16, 1)
RETURN
END
IF RIGHT(@folderName, 1) <> '/'
SET@folderName=@folderName + '/'
INSERTThemeColor
(
themeID,
label,
isActive,
folderName
)
VALUES(
@themeID,
@label,
@isActive,
@folderName
)
N 56°04'39.16"
E 12°55'05.25"
December 10, 2008 at 5:23 pm
Peso,
thank you so much. You taught me something. That works. The interesting part is my insert statement works in studio directly but not in the stored procedure. Don't know why.
I am wondering if I can put this stored procedure along the folowing statement
Exec pAddNewThemeScheme 4, 'Scheme2', 1, 'Scheme2'
in somesqlscript.sql. So another production database administrator can exec this somesqlscript.sql. It will accomplish creating the stored procedure and insert one record to the database without opening SQL server management studio.
do you have a better way? I don't know much about that.
Betty
December 10, 2008 at 5:38 pm
Please check with if the following will work.
assume that myScript.sql contains the stored procedure and
Exec pAddNewThemeScheme 4, 'Scheme2', 1, 'Scheme2' statement.
The an adminsitrator can run sqlcmd -S sqlservername\instanceName -i
myScript.sql
or using sql authentication:
sqlcmd -U login_id -P password -S sqlservername\instanceName -i
c:\myScript.sql
Then both stored procedure and one record is created in the database.
Thank you.
Betty
December 11, 2008 at 2:29 am
Create proc [dbo].[pAddNewThemeSCheme] (@themeID int, @label varchar(50), @isActive bit, @folderName varchar(50))
As
--themeID needs to be existant in the table Theme
Declare @TID int
Select @TID=themeID from dbo.Theme where themeID=@themeID
If @TID is NULL
print 'You need to create a theme before your creating theme scheme'
Else
If len(@label)<>0
if len(@label)>50
print 'lable name is too long'
Return
If len(@folderName)<>0
if len(@label) >50
print 'folder name is loo long'
Return;
--if all checks are OK
In the above code without using begin - end you are giving multiple statements. So, after checking with condition it is executing Return and coming out successfully.
December 12, 2008 at 7:31 am
This part of your code needs to be changed to something like the following, depending on what you are trying to accomplish:
Else
If len(@label)<>0 BEGIN
if len(@label)>50
print 'lable name is too long'
Return
END
If len(@folderName)<>0 BEGIN
if len(@label) >50
print 'folder name is loo long'
Return;
END
--if all checks are OK
If the code to execute after an IF statement is more than one statement it must be surrounded by BEGIN & END. Otherwise, it will a) test for the IF condition, b) if it's true, the first sql statement after the IF will be executed, c) whether it's true or not, then the second statement after the IF is always executed.
So in your code, it tests to see if label is > 50. Then if it is, it prints your statement. Then, whether it is or not it hits the "Return" command and executes it. So it always ends the stored procedure right after that test and it never gets past that.
Hope this helps.
- Cindy
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply