473,325 Members | 2,860 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Stupid Problem - Please help

Hi! I would be grateful for any advise regarding what I'm doing wrong.. My
brain is stuck. Probably some stupid simple mistake I don't see. Thanks very
much for your efforts!

Martin

I have this code:

DECLARE
@ContactID varchar(10),
@AddressID int,
@cmdSQL varchar(500)

Set @ContactID = '12'

SET @cmdSQL = 'SELECT AddressID FROM i2b_ash_contact WHERE ContactID = ' +
@ContactID

EXEC (@cmdSQL)

But how do I store the value returnd by EXEC (@cmdSQL) in the variable
@AddressID????

Tried several things unsucessfully e.g.:

EXEC @AddressID = @cmdSQL
SET @ AddressID = EXEC(@cmdSQL)

Am I really so stupid that I can't see what I'm doing wrong???
Jul 20 '05 #1
7 1724
[posted and mailed, please reply in news]

Martin Feuersteiner (th************@hotmail.com) writes:
I have this code:

DECLARE
@ContactID varchar(10),
@AddressID int,
@cmdSQL varchar(500)

Set @ContactID = '12'

SET @cmdSQL = 'SELECT AddressID FROM i2b_ash_contact WHERE ContactID = ' +
@ContactID

EXEC (@cmdSQL)


The first question is: why use dynamic SQL at all for the above?

SELECT @adrid = AddressID FROM i2b_ash_contact WHERE ContactID = @ContactID

is the simple way.

In case you really need to use dynamic SQL, sp_executesql is the way
to go. But there is a fair chance that even if your actual problem is
more complex, that you should not be using dynamic SQL at all.

Anyway, if you want to learn more about dynamic SQL, I have an article
on my web site, http://www.sommarskog.se/dynamic_sql.html. If you are
in a hurry, the solution to your problem is at
http://www.sommarskog.se/dynamic_sql.html#sp_executesql.
--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2
You don't need EXEC to do this.

SET @AddressID =
(SELECT AddressID
FROM i2b_ash_contact
WHERE ContactID = @ContactID)

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #3

"David Portas" <RE****************************@acm.org> wrote in message
news:fN********************@giganews.com...
You don't need EXEC to do this.

SET @AddressID =
(SELECT AddressID
FROM i2b_ash_contact
WHERE ContactID = @ContactID)

--
David Portas
------------
Please reply only to the newsgroup
--


Thanks everyone for replying. Well, I haven't mentioned that my table name
is dynamic, something like:

DECLARE
@AddressID int,
@ProgClient (varchar(10),
@Table varchar(10)

SET @Table = 'i2b_ + @ProgClient + '_contact

SET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)
I need to evaluate @Table first otherwise it can't find it.
Jul 20 '05 #4
> I need to evaluate @Table first otherwise it can't find it.

Unless you make it just one table and put the Progclient value in as a
column. Seems like an unusual design to have multiple tables of Contact
details.

CREATE TABLE Contacts (progclient VARCHAR(10), contactid INTEGER, addressid
INTEGER NOT NULL, ... PRIMARY KEY (progclient, contactid))

SET @AddressID =
(SELECT AddressID
FROM Contacts
WHERE ContactID = @ContactID
AND progclient = @ProgClient)

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #5
Thanks for your help Dave.
I've to admit, it's an unusual design but I've multiple contact tables named
e.g. i2b_ash_contact or i2b_ted_contact.
i2b_ and _contact are static but the middle part is dynamic. Reason for this
design is that I've a web application in which someone logs in and depending
on the login, a specific table for this login is used.
Storing all contacts in one table with an identifier of e.g. ash ted for
each record is not possible as from a security point, I need to keep
contacts for each login in a separate table.

So here we are again at the beginning. How can I do something like:

DECLARE
@AddressID int,
@ProgClient (varchar(10),
@Table varchar(10)

-- @Prog is e.g. 'ash'

SET @Table = 'i2b_ + @ProgClient + '_contact

SET @AddressID = (SELECT AddressID FROM @Table WHERE ContactID = @ContactID)

"David Portas" <RE****************************@acm.org> wrote in message
news:V9********************@giganews.com...
I need to evaluate @Table first otherwise it can't find it.
Unless you make it just one table and put the Progclient value in as a
column. Seems like an unusual design to have multiple tables of Contact
details.

CREATE TABLE Contacts (progclient VARCHAR(10), contactid INTEGER,

addressid INTEGER NOT NULL, ... PRIMARY KEY (progclient, contactid))

SET @AddressID =
(SELECT AddressID
FROM Contacts
WHERE ContactID = @ContactID
AND progclient = @ProgClient)

--
David Portas
------------
Please reply only to the newsgroup
--

Jul 20 '05 #6
> each record is not possible as from a security point, I need to keep
contacts for each login in a separate table.


Row-level security:

http://vyaskn.tripod.com/row_level_s..._databases.htm

--
David Portas
------------
Please reply only to the newsgroup
--
Jul 20 '05 #7
Thanks guys for answering my question.
I've spent some time on Erland's site and came to the conclusion that
I better follow a wise man's advice!

Have a nice day!

"David Portas" <RE****************************@acm.org> wrote in message news:<nJ********************@giganews.com>...
each record is not possible as from a security point, I need to keep
contacts for each login in a separate table.


Row-level security:

http://vyaskn.tripod.com/row_level_s..._databases.htm

Jul 20 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Martin | last post by:
Hi! I would be grateful for any advise regarding what I'm doing wrong.. My brain is stuck. Probably some stupid simple mistake I don't see. Thanks very much for your efforts! Martin I have...
119
by: rhat | last post by:
I heard that beta 2 now makes ASP.NET xhtml compliant. Can anyone shed some light on what this will change and it will break stuff as converting HTML to XHTML pages DO break things. see,...
6
by: Materialised | last post by:
Hi, could someone have a look at the following code and the error message below, and point out where my error is, as I am having trouble figuring where I am going wrong. #include <algorithm>...
16
by: Justin Hoffman | last post by:
This is a question concerning query optimisation. Sorry if it's a bit long, but thanks to anyone who has the patience to help - This is my first post here... If I have two tables:...
4
by: ad | last post by:
I have 50 web page in my project, there always a DataGrid in every page. The PreRender event is the same all of the DataGrid I have copy/paste the code of PreRender many many times. I think...
4
by: Smoothcoder | last post by:
Hi, there, smart guys! I have an AMD 64 3000+ based system, with Windows XP Professional on it; I recently installed Visual Studio 2003 Enterprise Architect; I created a new C# ASP.NET Web...
15
by: sparks | last post by:
We get more and more data done in excel and then they want it imported into access. The data is just stupid....values of 1 to 5 we get a lot of 0's ok that alright but 1-jan ? we get colums...
0
by: LadyJ | last post by:
Im coding in VB.NET and XAML for a project and have a problem creeping in is that when i check my combo box to see if there is anything in it so i can display appropriate image it does not work. ...
9
by: Carl Johansson | last post by:
If a multithreaded .NET application is executed on a computer with a multicore processor. Will the application automatically run the threads on different processor cores? Regards Carl Johansson
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.