Solved

SQL Stored Procedure variables

Posted on 2004-07-30
12
1,928 Views
Last Modified: 2008-03-03
Please help! I am sure this will be really easy for someone. I am a relative noob to TSQL and stored procedure syntax. I have a stored procedure that takes one argument, queries a table for a match to the argument, and sets the result equal to a variable (so I can use this result later on in the stored procedure). For some reason, this will not work...

Stored Procedure:
-------------------------------------------------------------------------
CREATE PROCEDURE dbo.sp_po_publishrequest
      @strPoUpdRequestID uniqueidentifier
AS
DECLARE @strPoUpdRequestFileID uniqueidentifier
SET @strPoUpdRequestFileID = (SELECT poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID)
SELECT @strPoUpdRequestFileID
GO
--------------------------------------------------------------------------

I keep getting this when I run the SP in SQL Query Analyzer:

[Microsoft][ODBC SQL Server Driver]Syntax error or access violation

Can anyone help?
0
Comment
Question by:jnelson35
12 Comments
 

Author Comment

by:jnelson35
ID: 11677071
Sorry, forgot to mention...this is SQL 2000 that I am dealing with (if it matters)
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11677105
try

CREATE PROCEDURE dbo.sp_po_publishrequest
     @strPoUpdRequestID uniqueidentifier
AS
SELECT poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID
GO
0
 
LVL 3

Expert Comment

by:pg_india
ID: 11677109
SET @strPoUpdRequestFileID = (SELECT poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID)

instead of thsi try:


SELECT@strPoUpdRequestFileID = poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID
0
 

Author Comment

by:jnelson35
ID: 11677174
pg_india,

I have tried both ways, neither works...I was using the SET method because I thought it was supposed to be the ANSI standard for setting variables.

hilarie,

Yours doesn't work either, which is really strange.
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11677277
How do you call the stored procedure ?

BOL Say
uniqueidentifier A globally unique identifier (GUID).

Remarks
A column or local variable of uniqueidentifier data type can be initialized to a value in two ways:
- Using the NEWID function.
- Converting from a string constant in the following form (xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx, in which each x is a hexadecimal digit in the range 0-9 or a-f). For example, 6F9619FF-8B86-D011-B42D-00C04FC964FF is a valid uniqueidentifier value.

Maybe you could explain what you intend to do with this stored procedure ?
0
 

Author Comment

by:jnelson35
ID: 11677352
Okay, this db is a sort of document index. Files are uploaded (in binary) to one table that also contains the FileID, FileName, FileType, and FileSize. The uploads are indexed in a separate table along with some other information and linked to the correct record in the file table by the FileID.

I want my SP to retrieve the FileID from the index table and then delete the appropriate record in the file table.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 26

Expert Comment

by:Hilaire
ID: 11677667
You don't need this two-step approch to delete a record from the table

You can use JOINs in a delete statement
eg

delete a
from Uploads a
INNER JOIN FileInfo b ON a.FileID = b.FileID
where b.FileName = 'FileToDelete'

then
delete from FileInfo where FileName = 'FileToDelete'

The whole thing could be wrapped in a stored procedure to handle transaction nicely

Please post table names + table structures if you need more support

Cheers

Hilaire
0
 

Author Comment

by:jnelson35
ID: 11678134
Okay...here are the details on my 2 tables...

tbl_po_web_temp
     |
     poUpdRequestID uniqueidentifier
     poID uniqueidentifier
     poName char(25)
     poTitle char(200)
     poOrg char(10)
     poNumPages int
     poReview datetime
     poUpdRequestDate datetime
     poUpdRequestRemarks char(200)
     poUpdRequestType smallint
     poUpdRequestFileID uniqueidentifier

tbl_po_docs
     |
     poUpdRequestFileID uniqueidentifier
     poUpdRequestFileType char(50)
     poUpdRequestFileSize varchar(50)
     poUpdRequestFileName char(50)
     poUpdRequestFileData image
0
 
LVL 5

Expert Comment

by:hkamal
ID: 11678332
Try this:

CREATE PROCEDURE dbo.sp_po_publishrequest
     @strPoUpdRequestID uniqueidentifier
AS
DECLARE @strPoUpdRequestFileID uniqueidentifier
SELECT @strPoUpdRequestFileID = poUpdRequestFileID FROM tbl_po_web_temp WHERE poUpdRequestID = @strPoUpdRequestID
SELECT @strPoUpdRequestFileID
GO

0
 
LVL 26

Expert Comment

by:Hilaire
ID: 11678483
May I also ask why you choosed the uniqueidentifier datatype rather than an int identity field (roughly the same than an oracle sequence, automatically generates incremental numbers) ?

Do you use replication between several databases ?

The following statement will delete records in tbl_po_web_temp
that match a given filename in tbl_po_docs

delete a
from tbl_po_web_temp a
inner join tbl_po_docs b on a.poUpdRequestFileID  = b.poUpdRequestFileID
where b.poUpdRequestFileName = 'file'


Below is an exemple stored procedure with trasaction handling to perform two deletes

create proc _usp_sampleproc_delete (@filename varchar(50)) as
begin transaction
delete a
from tbl_po_web_temp a
inner join tbl_po_docs b on a.poUpdRequestFileID  = b.poUpdRequestFileID
where b.poUpdRequestFileName = @filename
if @@error<> 0
begin
   rollback transaction
end
else
begin
   delete tbl_po_docs where poUpdRequestFileName = @filename
   if @@error = 0
   begin
      commit transaction
   end
   else
   begin
      rollback transcation
   end
end
go

exec proc _usp_sampleproc_delete 'yourfile.pdf'

You could use the same logic for an update statement

Hope this helps

Hilaire
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 125 total points
ID: 11678873
The odds are that you are attempting to call your procedure in QA like this:

Exec sp_po_publishrequest {DE8F841E-B3A8-4722-960A-A71A02312E52}

Add quotes around the uniqueidentifier, and it should work:

Exec sp_po_publishrequest '{DE8F841E-B3A8-4722-960A-A71A02312E52}'

NOTE:  you should also avoid using sp_ as the start of a stored procedure name, because SQL Server will look in the master database for any procedure with a name starting in sp_ before looking in the current database.  I tend to use usp_ - others use App or Concept starts (e.g. PO_PublishRequest)
0
 

Author Comment

by:jnelson35
ID: 11679040
Thank you all for help, but bhess1's solution answered my question. I will use all your tips as I continue to delve into the fun world of SQL.

Thanks again :)
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

914 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now