Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Accessing SQL variables within"WITH" statement

Posted on 2014-04-23
7
Medium Priority
?
224 Views
Last Modified: 2014-04-29
Hi
I'm trying to add an input variable (part_number) to my SQL Stored Proc but it seems to ignore the value I pass into the Stored Proc.

The only odd thing with the Stored Proc is that it uses the "WITH" convention to optimise it, and I'm wondering if you have to access a variable differently when used within the "WITH" convention? thanks
Fergal
0
Comment
Question by:fjkilken
  • 3
  • 2
  • 2
7 Comments
 
LVL 36

Expert Comment

by:ste5an
ID: 40017542
No,  but are you using this parameter in your sproc?
0
 

Author Comment

by:fjkilken
ID: 40017574
Yes, it's being refernced in the proc, I'm trying to pass a "part_number" value into the proc
0
 
LVL 36

Expert Comment

by:ste5an
ID: 40017714
Well, then you should post your procedure and it's invokation. Cause my Crystal Ball (tm) is not working right now..Crystal Ball (tm)
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 

Author Comment

by:fjkilken
ID: 40017762
ha ha - good point, I'm just trying to trim-down the query now to the pertinent code, but the issue is that now the parameter IS being recognised, so I'm trying to determine from the removed code as to what could be causing the parameter to be blocked.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 40026354
You do realize we have no idea what you are talking about, right?  

If you want help, try posting your query and explain what you are trying to achieve.
0
 

Author Comment

by:fjkilken
ID: 40027742
Thanks for the comments - after cleaning-up my query in prep. for posting it here, my variable is now functioning as expected, I'm not sure exactly what fixed it but it's now working.
In relation to this however, I need to be able to pass-in multiple values as in a SQL "IN" query, for example;
instead of simply using a single value-

WHERE @StockNumber = 'ABC'

I'd like to be able to do:

WHERE @StockNumber IN ('ABC','123','XYZ')

- is this possible?
Thanks!
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 1500 total points
ID: 40027992
Yes.  There are a number of ways (from good to bad):
1. Table-Valued parameters.
2. Xml
3. Delimted string.
4. Dynamic SQL.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

783 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