Stupid Problem - Please help

[Martin Feuersteiner]

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???

[Erland Sommarskog]

Re: Stupid Problem - Please help

[posted and mailed, please reply in news]

Martin Feuersteiner (theintrepidfox @hotmail.com) writes:[color=blue]
> 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)[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[David Portas]

Re: Stupid Problem - Please help

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
--

[Martin Feuersteiner]

Re: Stupid Problem - Please help


"David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:fNKdnbpX64 fbopjdRVn-hA@giganews.com ...[color=blue]
> 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
> --
>
>[/color]

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.

[David Portas]

Re: Stupid Problem - Please help

> 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
--

[Martin Feuersteiner]

Re: Stupid Problem - Please help

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" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
news:V9CdnYzrs4 LJ1ZjdRVn-gg@giganews.com ...[color=blue][color=green]
> > I need to evaluate @Table first otherwise it can't find it.[/color]
>
> 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,[/color]
addressid[color=blue]
> 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
> --
>
>[/color]

[David Portas]

Re: Stupid Problem - Please help

> each record is not possible as from a security point, I need to keep[color=blue]
> contacts for each login in a separate table.[/color]

Row-level security:

http://vyaskn.tripod.com/row_level_security_in_sql_server_databases.htm

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

[Martin]

Re: Stupid Problem - Please help

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" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message news:<nJKdnXujK M11G5jdRVn-sQ@giganews.com >...[color=blue][color=green]
> > each record is not possible as from a security point, I need to keep
> > contacts for each login in a separate table.[/color]
>
> Row-level security:
>
> http://vyaskn.tripod.com/row_level_s..._databases.htm[/color]