exec(select...), how supress the output?

Hi
I have a dynamically constructed sql query that I want to execute, e.g.
exec('select * from ' + @.tablename)

(1) Can I suppress the output somehow if this returns no values?
(2) Can I use the result of this query in another query somehow? e.g.
select
(3) Can I control the size of the columns in the output somehow

Thanks
F(foldface@.yahoo.co.uk) writes:
> I have a dynamically constructed sql query that I want to execute, e.g.
> exec('select * from ' + @.tablename)
> (1) Can I suppress the output somehow if this returns no values?

EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
@.tablename)

> (2) Can I use the result of this query in another query somehow? e.g.
> select

INSERT #tmp (...)
EXEC('...')

> (3) Can I control the size of the columns in the output somehow

This question is unclear. Output columns from SQL Server does not
really have any size, but that is up to the client tool you use.

But if you are using Query Analyzer, and want some nice output there,
you can use convert(varchar(n), ...) where n is the size of your choice.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> > (1) Can I suppress the output somehow if this returns no values?
> EXEC ('IF EXISTS (SELECT * FROM ' + @.tablename ' + ') SELECT * FROM ' +
> @.tablename)

worked this out in the end but as I'm using google for news access from
work I couldn't reply to my own mail. Thanks anyway

> > (2) Can I use the result of this query in another query somehow? e.g.
> > select
> INSERT #tmp (...)
> EXEC('...')

sorry, don't understand this? Can you elaborate?

> > (3) Can I control the size of the columns in the output somehow
> But if you are using Query Analyzer, and want some nice output there,
> you can use convert(varchar(n), ...) where n is the size of your choice.

create table #TempTable (id int)
insert #TempTable Values (convert(varchar(50), 2))
select * from #TempTable

I know this is meant to convert values but what exactly should I be seeing
here? I am talking about Query Analyser|||(foldface@.yahoo.co.uk) writes:
>> > (2) Can I use the result of this query in another query somehow? e.g.
>> > select
>>
>> INSERT #tmp (...)
>> EXEC('...')
> sorry, don't understand this? Can you elaborate?

You can save the output from an EXEC() statement in a temp table, and
then use the temp table in the next query.

>> > (3) Can I control the size of the columns in the output somehow
>>
>> But if you are using Query Analyzer, and want some nice output there,
>> you can use convert(varchar(n), ...) where n is the size of your choice.
> create table #TempTable (id int)
> insert #TempTable Values (convert(varchar(50), 2))
> select * from #TempTable
> I know this is meant to convert values but what exactly should I be seeing
> here? I am talking about Query Analyser

I don't know exactly what you are trying to achieve, and you might be
better off if you explained more about your business requirements.

But the column width in QA depends on two things: the column name and the
data type. If you want control over the column width, all output columns
must be varchar. You cannot control the width of a float or an int column.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.aspsql

Exec StoredProcedure in Query?

I have two stored procedures that have a common column and I need to write a query like this:

pseudo code:

Code Snippet

select * from (exec firstProcedure 'argument') T where T.ID not in (exec secondProcedure 'arg') S;

What's the proper way to do this?

It is not possible, Convert your Sp as inline table or table valued function (UDF).

Sample,

Code Snippet

Create Function firstProcedure (@.arg int)

returns table

return

(

Select id from Sysobjects

)

GO

Create Function secondProcedure (@.arg int)

returns table

return

(

Select id+10 id from Sysobjects

)

GO

Select * from firstProcedure(1) Where id not in (Select id from secondProcedure(2))

|||Another alternative is to create temp tables and use INSERT INTO ... EXEC proc syntax to load the data into two different temp tables and then to join the temp tables. My first choice is normally to do as ManiD suggested and create either a function or a view; however, there are some procedures that simply cannot be converted. If this is the case, the temp table option might work best.|||

You can also use functions OPENQUERY (if you added a linked server) or OPENROWSET.

Example:

SELECT TOP 10 *

FROM OPENROWSET('SQLOLEDB', '(local)';'my_user';'my_pwd', 'EXEC Northwind..[Ten Most Expensive Products]') as t

go

AMB

sql

EXEC stored procedure for every line of SELECT result table - how?

Hello,

Is it possible to EXEC stored procedure from a query?
I want to execute stored procedure for every line of SELECT result
table.

I guess it's possible with cursors, but maybe it's possible to make it
easier.

Give an example, please.

Thank you in advance.

Hubert<hubert.trzewik@.gmail.com> wrote in message
news:1110986308.176019.292290@.l41g2000cwc.googlegr oups.com...
> Hello,
> Is it possible to EXEC stored procedure from a query?
> I want to execute stored procedure for every line of SELECT result
> table.
> I guess it's possible with cursors, but maybe it's possible to make it
> easier.
> Give an example, please.
> Thank you in advance.
> Hubert

A cursor is probably the best solution, unless you can rewrite your stored
proc to operate on a set of data. First, you need to get the results from
your stored proc into a table (or convert the proc to a function) - this is
described here:

http://www.sommarskog.se/share_data.html

After that, you can do something like this:

declare @.c1 int, @.c2 int, ...
declare cur cursor fast_forward
for select col1, col2, ...
from dbo.TableWithProcResults -- or from dbo.MyTableFunction()

open cur
fetch next from cur into @.c1, @.c2...
while @.@.fetch_status = 0
begin
exec dbo.MyProc @.c1, @.c2, ...
fetch next from cur into @.c1, @.c2...
end

close cur
deallocate cur

Simon|||Thanks. It was quite fast and easy to create such procedure.

exec stored proc and use udf or select in it

Hi,
I have a stored procedure which has a cursor that populates a table
datatype variabel with few fields on eof which is of type [nText].
Now for each row in the above table type variabel I have to call
another stored proc to which I have to pass the [nText] field value.
Can anyone suggest how to do it?
Thanks,
Vikas.Could you call the stored procedure passing the ntext value before inserting
it into the table variable? You could call it after inserting as well, but
before fetching the next value from the cursor.
"vikassah@.gmail.com" wrote:

> Hi,
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
> Thanks,
> Vikas.
>|||(vikassah@.gmail.com) writes:
> I have a stored procedure which has a cursor that populates a table
> datatype variabel with few fields on eof which is of type [nText].
> Now for each row in the above table type variabel I have to call
> another stored proc to which I have to pass the [nText] field value.
> Can anyone suggest how to do it?
I don't really understand what you are trying to do. Maybe you could
be more specific?
Note, though that you cannot assign to variables of the type ntext,
so you may have to rework your strategy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Exec SP_Executesql @Selectstring

Hi,
How do I assign the select statement result to a variable?
Select @.Myvariable = Exec SP_Executesql @.Selectstring
It donesn't work.
Any ideas?
Thanksanswered in another newsgroup - please don't post the same message
independently to multiple newsgroups.

Exec SP_Executesql @Selectstring

Hi,
How do I assign the select statement result to a variable?
Select @.Myvariable = Exec SP_Executesql @.Selectstring
It donesn't work.
Any ideas?
Thanksanswered in another newsgroup - please don't post the same message
independently to multiple newsgroups.

Exec SP_Executesql @Selectstring

Hi,
How do I assign the select statement result to a variable?
Select @.Myvariable = Exec SP_Executesql @.Selectstring
It donesn't work.
Any ideas?
Thanks
answered in another newsgroup - please don't post the same message
independently to multiple newsgroups.

Exec SP many times, from select?

Hi All...

if i had the following sp...

*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************

and then i wanted to exec this sp in another procedure i would have

exec my_insert( 1_value, 2_value, 3_value )

My question is how could i exec this will the result set of a select... something like this

exec my_insert (select 1_value, 2_value, 3_value from another_table).

I know i could have this in an insert result type statement ie...

insert into dest_table (select 1_value, 2_value, 3_value from another_table)

but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement

Many Thanks
Gary Tyou can get the values into variables and send the variables into the sp.

select @.var1=select1_Value,@.var2=select2_Value,@.var3=select3_Value from YourTable
exec my_insert(@.var1,@.var2,@.var2)

hth|||Thanks for your help....but

This statement would only call the stored procedure once with the params set to the last selected values.

I would like to call the sp as many times as there are records in the select statement, containing the values for each record.

Cheers
Gary T|||Hi All,

If it's any help to describe the problem, I current carry out this by using a cursor, but I thought there must be an easier (better) way of doing this.

*****************************************************
DECLARE my_cursor CURSOR FOR
SELECT 1_value, 2_value, 3_value from other_table where some_condition

OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO
@.var_1, @.var_2, @.var_3
WHILE @.@.FETCH_STATUS = 0
BEGIN
exec my_insert @.var_1, @.var_2, @.var_3
FETCH NEXT FROM my_cursor
INTO
@.var_1, @.var_2, @.var_3
END
CLOSE my_cursor
DEALLOCATE my_cursor
*****************************************************

Cheers
Gary T|||cursors are usually a performance hit, but unless you are processing thousands of rows, it shud be ok. make sure you declare your cursor as :

DECLARE rs CURSOR
LOCAL
FORWARD_ONLY
OPTIMISTIC
TYPE_WARNING
FOR SELECT
OPEN rs
fetch next from rs into
WHILE ( @.@.FETCH_STATUS = 0 )
begin
FETCH NEXT FROM rs INTO
END
close rs
deallocate rs

the local,forward_only etc are optional parameters but will make it run faster. check out BOL for more info abt cursors and other parameters.

hthsql

EXEC or EXECUTE, SET or SELECT

Hi
pls find time to throw some light on
whether and where to use EXE/EXECUTE
and SET/SELECT?
What are the differences?
Another thing..if we start a trigger
and within the transaction statements
we first truncate any table and then
call rollback, will that table be
rolled back or not? What will be the
end result?
thanks in advance
Suresh Beniwal- SET
To assign a value to a variable
- SELECT vs SET
SELECT let you assign a value to multiple variables in the same statement.
Example:
declare @.d datetime
declare @.i int
select @.d = getdate(), @.i = @.@.error
- EXEC and EXECUTE
To execute a sp, both are the same because it if enough with the first four
letters.
execute sp_who2
exec sp_who2
To execute a string you use:
exec (string_var)
- begin transaction truncate table table_name rollback transaction
the table will be exactly that it was before the transaction.
AMB
"SureshBeniwal" wrote:

> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
> and SET/SELECT?
> What are the differences?
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not? What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>|||SET can only assign one value to a local variable. SELECT can assign more
than one. Other than that, there's no difference (as far as assigning local
variables is concerned). If you're saving the values of both @.@.ROWCOUNT and
@.@.ERROR, then you should definitely use SELECT because these are changed by
every statement.
EXEC is shorthand for EXECUTE.
Truncate table is a logged operation--even though it is minimally logged.
If it is executed within a transaction, a rollback will undo it.
"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message
news:1128003680.461221.32320@.g14g2000cwa.googlegroups.com...
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
> and SET/SELECT?
> What are the differences?
> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not? What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>|||"SureshBeniwal" <suresh.beniwal@.gmail.com> wrote in message news:1128003680.461221.32320@.g1
4g2000cwa.googlegroups.com...
> Hi
> pls find time to throw some light on
> whether and where to use EXE/EXECUTE
There is no difference betweeen EXEC and EXECUTE. EXEC is just an allowed a
bbreviation.
EXE however does nothing.

> and SET/SELECT?
> What are the differences?
Set is specifically used for setting variables, select can be used that way
,
but is more normally for entire result sets, and returns them to the client
I always use SET where they both work

> Another thing..if we start a trigger
> and within the transaction statements
> we first truncate any table and then
> call rollback, will that table be
> rolled back or not?
Yes. Until it's committed truncate is undoable. It is faster / more effici
ent because
it writes less to the transaction log, but it is still transactionable !
30 sec test:
create table Test ( SomeID int identity, SomeOthercol varchar(20) )
go
insert Test ( SomeOtherCol) values ('One')
insert Test ( SomeOtherCol) values ('Two')
insert Test ( SomeOtherCol) values ('Three')
insert Test ( SomeOtherCol) values ('Four')
insert Test ( SomeOtherCol) values ('Five')
go
begin tran
delete Test
select * from test
rollback tran
select * from test
begin tran
truncate table Test
select * from Test
rollback tran
select * from Test
What will be the
> end result?
> thanks in advance
> Suresh Beniwal
>
Regards
AJ|||Hi All,
Thanks a lot for those basic concepts
Regards,
Suresh Beniwal

Exec not returning a result

Hi all,
How can I tell if a executed dynamically created sql is returning no rows.
As in:
select @.sql= 'insert into PLImport2 select * from
OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel
8.0;Database=C:\work\coke\Detailed P&L June RE.xls'', ''SELECT top 5000 *
FROM [Import1] where F1 > '+ltrim(str(@.Rowid))+''')'
exec (@.sql)
Which whould normally retun and insert 5000 rows. I want to this in a loop,
but am afraid to because as soon as it does not find any rows, I want it to
stop. At the momen it can carry on for ever
Thanks
RobertDoes it make a difference that you are INSERTing into [PLImport2] and you
are SELECTing from [Import1]?
Arnie Rowland*
"To be successful, your heart must accompany your knowledge."
"Robert Bravery" <me@.u.com> wrote in message
news:%23TrjLTunGHA.4952@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> How can I tell if a executed dynamically created sql is returning no rows.
> As in:
> select @.sql= 'insert into PLImport2 select * from
> OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',''Excel
> 8.0;Database=C:\work\coke\Detailed P&L June RE.xls'', ''SELECT top 5000 *
> FROM [Import1] where F1 > '+ltrim(str(@.Rowid))+''')'
> exec (@.sql)
> Which whould normally retun and insert 5000 rows. I want to this in a
> loop,
> but am afraid to because as soon as it does not find any rows, I want it
> to
> stop. At the momen it can carry on for ever
> Thanks
> Robert
>|||HI,
Yes, PLImport2 is a SQL Table that I am inserting into, and Import1 is
range in an excel File. I am selecting data from the Excel file into the SQL
table
Thanks
Robert
"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eWIQ2VunGHA.4104@.TK2MSFTNGP04.phx.gbl...
> Does it make a difference that you are INSERTing into [PLImport2] and you
> are SELECTing from [Import1]?
> --
> Arnie Rowland*
> "To be successful, your heart must accompany your knowledge."
>
> "Robert Bravery" <me@.u.com> wrote in message
> news:%23TrjLTunGHA.4952@.TK2MSFTNGP02.phx.gbl...
rows.
*
>

EXEC in stored procedure

Hello!
I'm trying to use
EXEC ('SELECT * FROM Categories')
into a Stored Procedure, but the users get permission denied on object
'Categories'
If I'm using
SELECT * FROM Categories
everything works.
The user haven't any permission of Categories, but the SP should have it.
Is the EXEC command inside the SP run as the user? Why?
Regards MagnusHi Magnus
The dynamic select doesn't obey the same ownership chaining rules as
statements outside the EXEC. Users running the stored procedure must have
explicit permissions on the objects in the dynamic SQL.
A SELECT outside of the EXEC obey ownerships chaining rules, and any user
who has execute permission on procedure can run the statements in the
procedure, as long as the procedure and the table have the same owner.
Why do you need the EXEC? It serves no purpose here.
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Magnus Blomberg" <magnus.blomberg@.skanska.se> wrote in message
news:utP4%2373uEHA.4084@.TK2MSFTNGP10.phx.gbl...
> Hello!
> I'm trying to use
> EXEC ('SELECT * FROM Categories')
> into a Stored Procedure, but the users get permission denied on object
> 'Categories'
> If I'm using
> SELECT * FROM Categories
> everything works.
> The user haven't any permission of Categories, but the SP should have it.
> Is the EXEC command inside the SP run as the user? Why?
> Regards Magnus
>|||When you use:
EXEC ('SELECT * FROM Categories')
... you are using dynamic SQL. Thus, the person running it must have their
permissions checked against the underlying objects - Categories, in this
case.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"Magnus Blomberg" <magnus.blomberg@.skanska.se> wrote in message
news:utP4%2373uEHA.4084@.TK2MSFTNGP10.phx.gbl...
Hello!
I'm trying to use
EXEC ('SELECT * FROM Categories')
into a Stored Procedure, but the users get permission denied on object
'Categories'
If I'm using
SELECT * FROM Categories
everything works.
The user haven't any permission of Categories, but the SP should have it.
Is the EXEC command inside the SP run as the user? Why?
Regards Magnus|||> Is the EXEC command inside the SP run as the user? Why?
One reason I can think of is because dynamic SQL is a very serious security
concern, and if you allow any table name -- or any SQL command, for that
matter -- to run, just because the user can run the stored procedure, then a
user can wreak havoc on your system quite easily.
So, the engine verifies permission once it resolves the dynamic SQL...
A|||Hi all of you!
Well, I thought I should get that question. This message is written at home
without any SQL, so it might be small code errors.
First I can tell you, this SP doesn't accept any input parameters, so I
think the security riscs are quite small.
I must also say, that the system is rather old and is using application
role, so the objects aren't made for any user access for the users.
The reason for using dynamic SQL is that the SP will collect info about
tables into table #tbls as TableName, IDColumn, ValueColumn etc.
Then this is used by a Cursor to add data from the tables specified in #tbls
to table #rows.
Eg: INSERT #tbl (@.IDColumn,@.ValueColumn) INTO #rows FROM @.TableName
Correct me if I'm wrong but the row above is not valid, because it's not
using dynamic SQL, yea?!?!
Then the SP should return as SELECT * FROM #rows
This is the complete purpose, and I found this very difficult not using
EXEC...
Any ideas how to do the similar without using dynamic SQL.
Regards Magnus
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OgfAhO4uEHA.3456@.TK2MSFTNGP14.phx.gbl...
> One reason I can think of is because dynamic SQL is a very serious
security
> concern, and if you allow any table name -- or any SQL command, for that
> matter -- to run, just because the user can run the stored procedure, then
a
> user can wreak havoc on your system quite easily.
> So, the engine verifies permission once it resolves the dynamic SQL...
> A
>

exec in store procedure, what is in user define function?

I have 3 string
select @.string = 'SELECT'
select @.string2 ='colname'
select @.string3 ='tablename'
select @.finalstring = @.string + @.string2 + @.string3
inside the user define function, we can not put in
exec command, how we run this dynamic sql command in the
user define function?
What i can replace the exec(@.finalstring) in store
procedure to excute the string in user define function?
When i put the exec in the user define function, it says,
can excute in the user define function.
thanks
regards,
florenceSQL Server need to know what the UDF will return, and a lot of other stuff. I.e., you cannot use
dynamic SQL in UDF's.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"florencelee" <florencelee@.visualsolutions.colm.my> wrote in message
news:067a01c49f9f$948d4840$a601280a@.phx.gbl...
> I have 3 string
> select @.string = 'SELECT'
> select @.string2 ='colname'
> select @.string3 ='tablename'
> select @.finalstring = @.string + @.string2 + @.string3
> inside the user define function, we can not put in
> exec command, how we run this dynamic sql command in the
> user define function?
> What i can replace the exec(@.finalstring) in store
> procedure to excute the string in user define function?
> When i put the exec in the user define function, it says,
> can excute in the user define function.
> thanks
> regards,
> florence

EXEC date problem

Hello all,
I have a standard stored procedure that contains the following code:

SET @.SQL = 'SELECT id, building, firstname, lastname, closed, assignto, vandalism, entered_by, phone, roomno, status, dateentered, date_closed AS DATECLOSED FROM requests '
SET @.SQL = @.SQL + 'WHERE building_no IN (' + @.BUILDINGLIST + ') AND STATUS_NO = ' + @.STATUS + ' '

Works fine, but my dates come back unformated i want to replace the above:

dateentered and date_closed

with

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED
CONVERT(varchar(30), date_closed, 101) AS DATECLOSED

but, when i do, i receive the following error:

ERROR CLOSE TO AS

Does anyone have any ideas? Thanks in advance.

Richard M.what's it look like when you've already replaced them?

CONVERT(varchar(30), dateentered, 101) AS DATEENTERED

also, dateentered appears to be a field that doesn't exist.

EXEC as part of a SELECT

I wrote a stored proc that results in a dynamic rowset of different columns.
(not predefined except ID). To Make it available for a join with i.e. a view
,
I want to use an inline-tablereturn function, because this kind of function
doesnt need a predefined Tabledefinition, too.
But: I have just one Select-Statement to work with. And I dont know how to
hook them together.
something like:
Select * From Exec <myproc> <myparams>
(does somebody know how to work with derived tables? its probably a way)Klaus,
Might check out Erland's article:
How to share data between stored procedures
http://www.sommarskog.se/share_data.html
HTH
Jerry
"KlausSarbeach" <KlausSarbeach@.discussions.microsoft.com> wrote in message
news:C87327E7-8988-4DF5-96DB-6F95F9E2AAEC@.microsoft.com...
>I wrote a stored proc that results in a dynamic rowset of different
>columns.
> (not predefined except ID). To Make it available for a join with i.e. a
> view,
> I want to use an inline-tablereturn function, because this kind of
> function
> doesnt need a predefined Tabledefinition, too.
> But: I have just one Select-Statement to work with. And I dont know how to
> hook them together.
> something like:
> Select * From Exec <myproc> <myparams>
> (does somebody know how to work with derived tables? its probably a way)
>

exec an SP to return rows to another SP

Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??

Something like...

CREATE procedure spThisSP(@.param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @.param)

CheersYou could re-write the sub proc as a function|||Thanks for the reply pkr but I was hoping that I could re-use stored procedures somehow?
Duplicating the code one way or another seems "wrong"...

Cheers|||You could use a temp table...
select into #temp
exec proc1

select * from #temp

But I HATE temp tables.|||i didnt understand what you are trying to do but i know you can call a stored proc from another stored proc..

hth|||pkr,

But I HATE temp tables.

May I ask why? I'm not being critical, just trying to understand various people's like and dislike of temp tables.

Thanks,
Don|||My main hate is a really irrating bug/feature I've hit in the past where the db is convinced the temp table already exists when you try to create it but says it doesn't exist when you try to use it!!

Besides that, there are too many locking issues with tempDb that make it very difficult to effectively police developer code - IMO. I just try to avoid them wherever possible.|||Cool, thanks.

Don|||Thanks for all the replies. From your discussion I found thisarticle which provides some more helpful pointers in using temp tables.

Exec a StoredProc from in a Select statement that returns mult

Tony,
The called sp is too big to put in the SELECT. It does a lot, creates and
used temp tables. . . I don't think I would want to go that route.
Because it uses temp tables, I couldn't make it a udf. I have not used a
scalar function. How does it differ from a udf? Can I create and use temp
tables in it?
Thanks,
Steve
"Tony Sellars" wrote:
> Sounds like you have a couple of options. One would be to incorporate the
> logic from the procedure you are calling into the select statement you
> mentioned. Another would be to create a scalar function which returns the
> output1 value you are looking at and use it in place of the subselect in y
our
> sample. The function option would most likely not perform as fast as
> incorporating the logic directly.
> HTH
> --Tony
> "SteveInBeloit" wrote:
>Scalar functions are simply a type of udf (take a look at bol under 'create
function' for all the details). Given what you are saying here are some
other options. The functions can use table variables so it may be possible
to write your procedure as a function depending on what your code does (ther
e
are some coding limitations within functions). Another option would be to
rewrite your spMyProc to take a set of values (say in an xml parameter) and
return a result set which either matches what you would ultimately select ou
t
(and not write the calling procedure) or that could be used in an INSERT
EXEC statement to trap the results in a temp table and be used in the final
join.
Hope this does more than just muddy the water for you.
--Tony
"SteveInBeloit" wrote:
> Tony,
> The called sp is too big to put in the SELECT. It does a lot, creates and
> used temp tables. . . I don't think I would want to go that route.
> Because it uses temp tables, I couldn't make it a udf. I have not used a
> scalar function. How does it differ from a udf? Can I create and use tem
p
> tables in it?
> Thanks,
> Steve
> "Tony Sellars" wrote:
>

Exec @string

I am trying to do an insert statement utilizing a variable string.

something like:

Set @.cString = 'SELECT top 10 *
FROM OPENDATASOURCE(
' + char(39) + 'SQLOLEDB' + char(39) + ',' + char(39) +
'Data Source=' + @.lServer + ';User ID=' + @.user + ';Password=' + @.pword + char(39) + '
).myServer..

Insert into #Temp_table (field1, field2)
select exec @.cString

--What is the syntax for this?as answered in previous post. you can like so:

insert tb
exec(@.sql)|||When I do something like:

Insert into #tACCOUNT (ACCOUNTID, ACCOUNT, TYPE)
exec (@.cString)

I get:

MSDTC on server 'myServer' is unavailable.|||dtc is needed to ensure data integrity between 2 sites. when you do any dml, the transaction is implicitly promoted to a distributed transaction.

EXEC (select... ) problem Help!

How can I get this to work?
declare @.WkEmpID
declare @.sql varchar(1000)
set @.sql = 'select distinct @.WkEmpID = EmpID from Employee'
exec (@.sql)
Now this is a simplified version of a more comples query which is forcing me
to use this method rather than a simple SQL query. The big problem is getting
the 'EmpID' value into the local variable of '@.WkEmpID'. Please help!
On Sat, 6 Aug 2005 08:20:03 -0700, Scagnetti wrote:

>How can I get this to work?
>declare @.WkEmpID
>declare @.sql varchar(1000)
>set @.sql = 'select distinct @.WkEmpID = EmpID from Employee'
>exec (@.sql)
>Now this is a simplified version of a more comples query which is forcing me
>to use this method rather than a simple SQL query. The big problem is getting
>the 'EmpID' value into the local variable of '@.WkEmpID'. Please help!
Hi Scagnetti,
First: do try to solve this without dynamic SQL. There are often other
ways to get the same result. Consider posting your real problem here;
maybe someone sees a solution you didn't think of. For a discussion and
the pro's and (mainly) con's of dynamic SQL, visit Erland Sommarskog's
site: http://www.sommarskog.se/dynamic_sql.html.
The answer to your question is to use the stored procedure sp_executesql
instead of exec (@.sql). Details in Books Online (and on Erland's page).
But as I said - doing it without dynamic SQL is the best solution.
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Here's an example on how to build a dynamic SQL string and run it with
EXECUTE or SP_EXECUTESQL stored procedure
http://www.dandyman.net/sql/samples/dynamicsql.txt
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Scagnetti" <Scagnetti@.discussions.microsoft.com> wrote in message
news:7363AFA0-2098-4014-9BEB-FFF6F5D697DC@.microsoft.com...
> How can I get this to work?
> declare @.WkEmpID
> declare @.sql varchar(1000)
> set @.sql = 'select distinct @.WkEmpID = EmpID from Employee'
> exec (@.sql)
> Now this is a simplified version of a more comples query which is forcing
> me
> to use this method rather than a simple SQL query. The big problem is
> getting
> the 'EmpID' value into the local variable of '@.WkEmpID'. Please help!
sql

Excluding part of select statement if no data is returned in results

I have a query that returns results based on information in several tables. The problem I am having is that is there are no records in the one table it doesn't return any information at all. This table may not have any information initially for the employees so I need to show results whether or not there is anything in this one table.

Here is my select statement:

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

The section below is the part that may or may not contain information:

SELECT (SELECTSUM(request_duration)AS daystakenFROM request)AS daysleft

So I need it to return results whether this sub query has results or not. Any help would be greatly appreciated!!!

TIA

BUMP... Somebody...|||

Okay, I tried adding the ISNULL to the statement, but I think the problem is because until a request has been put in there is nothing linking the employee table for the JOIN on the request table. When they put in a request it adds an entry to the request table for them. Up till that point, there will be nothing matching the two tables.

Here is my statement as it stands now. Is there anyway to get the results to show if the INNERJOIN isn't finding any results in the request table?

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeINNERJOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

I know it seems I am just talking to myself at this point, but I would LOVE for someone to join my conversation. Thanks in advance for any help!!!

|||

Okay, I figured it out. Had to switch my query to a LEFT OUTER JOIN.

SELECT employee.emp_id,DATEDIFF(mm, employee.emp_begin_accrual,GETDATE()) * employee.emp_accrual_rate - (SELECTSUM(ISNULL(request_duration,'0'))AS daystakenFROM request)AS daysleft, employee.emp_lname +', ' + employee.emp_fname +' ' + employee.emp_minitial +'.'AS emp_name, department.department_name, location.location_nameFROM employeeLEFTOUTER JOIN requestAS request_1ON employee.emp_id = request_1.emp_idINNERJOIN departmentON employee.emp_department = department.department_idINNERJOIN locationON department.department_location = location.location_idGROUP BY employee.emp_id, employee.emp_begin_accrual, employee.emp_accrual_rate, employee.emp_fname, employee.emp_minitial, employee.emp_lname, department.department_name, location.location_nameORDER BY location.location_name, department.department_name, employee.emp_lname

excluding fields in SELECT statement

Hi,

Is there a way to exclude fields in a query other than just including
the ones you want. If there are 20 fields and you want to see all but
3, it would be a lot easier to exclude the 3.

Thanks"ric" <rtavert@.yahoo.com> wrote in message
news:1130086527.567870.112960@.z14g2000cwz.googlegr oups.com...
> Hi,
> Is there a way to exclude fields in a query other than just including
> the ones you want. If there are 20 fields and you want to see all but
> 3, it would be a lot easier to exclude the 3.
> Thanks

No. It isn't difficult to list just the columns you want. In Query Analyzer
you can drag the column list from the Object Browser pane so no typing is
required.

Note that it is also good practice to avoid using SELECT * in production
code. Using * instead of listing just the required columns is not only
inefficient, it also impacts the reliability and maintainability of your
code.

--
David Portas
SQL Server MVP
--|||No, there is no Transact-SQL syntax for this. However, you can use Query
Analyzer to generate a SELECT statement for the desired view or table and
then remove the unneeded columns.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"ric" <rtavert@.yahoo.com> wrote in message
news:1130086527.567870.112960@.z14g2000cwz.googlegr oups.com...
> Hi,
> Is there a way to exclude fields in a query other than just including
> the ones you want. If there are 20 fields and you want to see all but
> 3, it would be a lot easier to exclude the 3.
> Thanks|||>> Is there a way to exclude fields [sic] in a query other than just including the ones you want. If there are 20 fields [sic] and you want to see all but 3, it would be a lot easier to exclude the 3. <<

Short answer: No. Have you ever seen such a thing in any other
programming language? The closest thing I know is FILLER in Cobol.

Rows are not records; fields are not columns; tables are not files. It
is not easier; it is dangerous. If the base table is altered, your
syntax would not see the changes and would screw up. A list of column
name is easy to generate from the schema information tables with a
tool.|||Great!

Forgot to mention I was looking for a faster way to work in the Query
Analyzer.

>>you can use Query Analyzer to generate a SELECT statement for the desired view...

Is this something other than dragging over columns into the SELECT
statement you're building?

Thanks,
Ric|||ric (rtavert@.yahoo.com) writes:
>>>you can use Query Analyzer to generate a SELECT statement for the desired
>>>view...
> Is this something other than dragging over columns into the SELECT
> statement you're building?

I guess this is what Dan had in mind. Myself, when I need to do this, I
usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
the headers into Textpad, where I have a macro so I with a keypress can
replace the spaces with commas, and then cut and paste back.

There is a new product PromptSQL which claims to provide intellisense to
Query Analyzer. Since I am not fond of intellisense myself, I have not
tried it. But somehing that expands a * would be a great thing for such a
tool - maybe they have it?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||> I guess this is what Dan had in mind.

I used to use that method in the old days (pre- SQL 2000) but I was
referring to the following technique:

1) right-click on the desired table in the QA object browser

2) select Script Object to New Window as--> Select

3) remove unwanted columns

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns96F9F4031F9D6Yazorman@.127.0.0.1...
> ric (rtavert@.yahoo.com) writes:
>>>>you can use Query Analyzer to generate a SELECT statement for the
>>>>desired
>>>>view...
>>
>> Is this something other than dragging over columns into the SELECT
>> statement you're building?
> I guess this is what Dan had in mind. Myself, when I need to do this, I
> usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
> the headers into Textpad, where I have a macro so I with a keypress can
> replace the spaces with commas, and then cut and paste back.
> There is a new product PromptSQL which claims to provide intellisense to
> Query Analyzer. Since I am not fond of intellisense myself, I have not
> tried it. But somehing that expands a * would be a great thing for such a
> tool - maybe they have it?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||Expanding '*' is something people have asked for, and its on the list,
but not there yet -
http://www.promptsql.com/known_bugs...nhancements.htm

Regards,
Damian (PromptSQL developer)

Erland Sommarskog wrote:
> ric (rtavert@.yahoo.com) writes:
> >>>you can use Query Analyzer to generate a SELECT statement for the desired
> >>>view...
> > Is this something other than dragging over columns into the SELECT
> > statement you're building?
> I guess this is what Dan had in mind. Myself, when I need to do this, I
> usually do a SELECT * FROM tbl WHERE 1 = 0 in text mode, copy and paste
> the headers into Textpad, where I have a macro so I with a keypress can
> replace the spaces with commas, and then cut and paste back.
> There is a new product PromptSQL which claims to provide intellisense to
> Query Analyzer. Since I am not fond of intellisense myself, I have not
> tried it. But somehing that expands a * would be a great thing for such a
> tool - maybe they have it?
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp|||I said expanding '*' was on the list of things to be implemented --
we've just released PromptSQL 1.2 Beta one, and this was one of the new
features.

You can now type
SELECT *[TAB] FROM Orders, Customers
and the * will be expanded to insert all the columns in Orders and
Customers, prefixed by appropriate aliases. The separator is
customizable.

Or you can type:
SELECT o.*[TAB] FROM Orders o, Customers and the o.* will be replaced
by a list of all Orders columns, prefixed by "o."

Regards,
Damian (PromptSQL developer)

http://www.promptsql.com