SQL Server - Using EXEC within a SELECT statement

Hi.  I have a SELECT storedproc named spGetUnit which returns a single scalar
value.

I'm trying to create a separate SELECT stored which executes spGetUnit
within it's SELECT command.  Here is how I have it-

Select E.EmpName,E.Salary,Unit=exec [spGetUnit] @EmployeeID=E.EmpName
FROM Employees E

But the syntax is invalid and I receive the error: "Incorrect syntax near
the keyword 'exec'."

Is it possible to use EXEC within a SELECT statement, as I am trying?

Thanks,
Mike

  Mike C# replied...

No.  There might be other options, however.  Like UDFs or temp table/table
variable.  It depends on your actual code though.

  Mik replied...

Thanks for the reply.

  vt replied...

hi,

no, you cannot execute sp as column name in select statement, try function


--
VT
Knowledge is power, share it....
http://oneplace4sql.blogspot.com/

  M replied...

You might find some pointers (and examples, and much more) in this article by
Erland Sommarskog:
http://www.sommarskog.se/share_data.html


ML

---
http://milambda.blogspot.com/

Date: 12 / 31 / 2008 10:56:11 * ** ** * / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO create procedure [dbo].[sp_DTA_start_xmlprefix] as begin declare @startTags nvarchar(128) set @startTags = N'<DTAXML> <DTAOutput> <AnalysisReport> ' select @startTags end GO / * ** ** * Object: Table [dbo].[DTA_input] Script Date: 12 / 31 / 2008 10:56:59 * ** ** * / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_input]( [SessionName] [sysname] NOT NULL, [SessionID] [int] IDENTITY(1, 1) NOT NULL, [TuningOptions] [ntext OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO / * ** ** * Object: Table [dbo].[DTA_progress] Script Date: 12 / 31 / 2008 10:57:29 * ** ** * / SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[DTA_progress]( [ProgressEventID] [int] IDENTITY(1, 1) NOT NULL, [SessionID] [int] NULL, [TuningStage] [tinyint] NOT nvarchar](256) NULL, [PerformingAnalysisMessage] [nvarchar](256) NULL, [GeneratingReportsMessage] [nvarchar](256) NULL ) ON [PRIMARY] GO / * ** ** * Object: Table [dbo].[DTA_output] Script Date: 12 / 31 / 2008 10:57:10 * ** ** * / SET ANSI_NULLS ON GO SET

another FG called FG_INDEX Thanks Javier SQL Server Discussions MovePKAndAllUniqueConstraints (1) TargetFileGroupID (1) SourceFileGroupID (1) CREATE TABLE (1) ALTER TABLE (1) PRIMARY KEY (1) FOREIGN KEY (1) DROP INDEX (1) One way is to create or re-create their clustered indexes on the new filegroup. For instance to move table named junk from PRIMARY to FG_DATA: alter database test add filegroup FG_DATA; alter database test name = 'test2', filename = 'c: \ test2.dat') to filegroup FG_DATA - - if there is no clustered already, create a clustered index on FG_DATA create clustered index tmp_idx on junk(i) on FG_DATA; - - Now check if the user table has

trace what a few people are doing in a few of my DBs. I can create a gui trace and get the events as I want to see, then I script without leaving profiler on. When I run my script it looses all the filtering and column orders. Can anyone tell me how this could happen? I have the filters set and Joe / * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * / / * Created by: SQL Server Profiler 2005 * / / * Date: 05 / 21 / 2008 02:39:56 PM * / / * ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** ** * / - - Create a Queue declare @rc int declare @TraceID int declare @maxfilesize bigint set @maxfilesize = 5 - - Please please use UNC path and make sure server has - - write access to your network share exec @rc = sp_trace_create @TraceID output, 6, N' \ vmdevserver4 \ TraceFiles \ MyTrace', @maxfilesize, NULL if (@rc ! = 0) goto error - - Client side File and Table cannot be scripted - - Set the events declare @on bit set @on = 1 exec sp_trace_setevent @TraceID, 14, 7, @on exec sp_trace_setevent @TraceID, 14, 23, @on exec sp_trace_setevent @TraceID, 14, 8, @on exec sp_trace_setevent @TraceID, 14, 64, @on exec sp_trace_setevent @TraceID, 14

Sainsburys (1) SQL Server 2005 (1) SainsburysADSZJG (1) SQL01.Sainsburys (1) Uniqueidentifier (1) CareACICodes (1) CREATE TABLE (1) PRIMARY KEY (1) These articles explain what you want to do: How to manually of procs on your subscriber) ues Thanks for your inputs. What i did was to create a local subscription database and then backed it up and restored into the remote server agent starts up and straight away goes to the step of bulk copying data into table MSMerge_contents. usually you expect to see another row depicting how many rows were bulk copied transaction capable: 2 read only: N identifier quote char: " non_nullable_columns: 1 owner usage: 31 max table name len: 128 max column name len: 128 need long data len: Y max columns in table: 1024 max columns in index: 16 max char literal len: 524288 max statement len: 524288 transaction capable: 2 read only: N identifier quote char: " non_nullable_columns: 1 owner usage: 31 max table name len: 128 max column name len: 128 need long data len: Y max columns

Temp tables vs Permanent table with deletes SQL Server I am looking at changing about 10 procedures that use a Permanent table for nothing more than temporary storage to load and store various records from a select statement and run a Join on this table to update other records. It then deletes all the records it put into the table. The table is used by multiple procedures at the same time keyed by a Guid it always gets at the start of the routines. There are about 4 indexes on this table. There can be any from 1 to 1000 records involved in this. I was thinking about dropping the table and just using a temp table. When the table is created I would then have to create 3 indexes - would drop