Solved

SQL Stored Procedure variables

Posted on 2004-07-30
12
1,927 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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ssms - object execution statistics 12 37
MS SQL Backup 24 70
SQL Connection (Error 18456) 14 34
sql query Help 12 29
In this article—a derivative of my DaytaBase.org blog post (http://daytabase.org/2011/06/18/what-week-is-it/)—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

758 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

18 Experts available now in Live!

Get 1:1 Help Now