Exec commnad - problems

Question

Hi,

 

I have some problems with the Exec commnad. All problems are related to the following table:

 

CREATE TABLE [dbo].[Quz](
	[SerID] [bigint] IDENTITY(1,1) NOT NULL,
	[ParentID] [bigint] NULL,
	[Type] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Title] [nvarchar](300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Cntnt] [ntext] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Path] [nvarchar](2000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LastEditedBy] [bigint] NULL,
	[LastEditedDate] [datetime] NULL,
 CONSTRAINT [PK_Quz] PRIMARY KEY CLUSTERED 
(
	[SerID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]



SET IDENTITY_INSERT [dbo].[Quz] ON
INSERT [dbo].[Quz] ([SerID], [ParentID], [Type], [Title], [Cntnt], [Path], [LastEditedBy], [LastEditedDate]) VALUES (427, 1, N'Category  ', N'Temp', NULL, NULL, 1, CAST(0x00009FB20080C0C2 AS DateTime))
INSERT [dbo].[Quz] ([SerID], [ParentID], [Type], [Title], [Cntnt], [Path], [LastEditedBy], [LastEditedDate]) VALUES (428, 427, N'Document  ', N'Temp document1', N'Temp document 1', N'428-Temp-document1.aspx', 1, CAST(0x00009FB20080CC50 AS DateTime))
INSERT [dbo].[Quz] ([SerID], [ParentID], [Type], [Title], [Cntnt], [Path], [LastEditedBy], [LastEditedDate]) VALUES (429, 427, N'Category  ', N'Sub - Temp 1', NULL, NULL, 1, CAST(0x00009FB20080D909 AS DateTime))
INSERT [dbo].[Quz] ([SerID], [ParentID], [Type], [Title], [Cntnt], [Path], [LastEditedBy], [LastEditedDate]) VALUES (430, 429, N'Document  ', N'Temp document2', N'Temp document 2', N'430-Temp-document2.aspx', 1, CAST(0x00009FB20080F385 AS DateTime))
SET IDENTITY_INSERT [dbo].[Quz] OFF

1) The following sql syntax enables the user to select a parent record and all its children from a table.

I need to add a primary key column to the table (I need to set serial id for the result table records starting from ID=1 and so on).

 Here is how I get the parent record and all its children:

 

Declare @TableName nvarchar(50) 
Set @TableName = 'Quz' 
Declare @TargetTableName nvarchar(50) 
Set @TargetTableName = 'Osn' 
DEclare @SerID bigint 
Set @SerID = 427 

Exec(N' 
	WITH ret AS( 
		SELECT * FROM ' + @TableName + N' 
		WHERE SerID = ' + @SerID + N' 
		UNION ALL 
		SELECT t.* FROM ' + @TableName + N' t INNER JOIN ret r ON t.ParentID = r.SerID 
	)
	
	SELECT * from ret
')

 

Screen-shot of the result:

 

2)

The following sql syntax in intended to set values in local variables using a sysntax which is placed within the Exec commnad. But I got the following an error message:

Declare @TableName nvarchar(50) 
Set @TableName = 'Quz' 
DEclare @SerID bigint 
Set @SerID = 427 

Declare @Type nchar(10)

Exec(N' 
	select @Type = (Select [Type] from ' + @TableName + N'  where [SerID] = ' + @SerID + ')
')

Select @Type

Note: the @Type is intended to be used again (I mean, that the last syntax is only a part of the entire syntax)

The error message which I got is:

Msg 137, Level 15, State 1, Line 2
Must declare the scalar variable "@Type".

Please, I need your help in order to solve the above two problems

 

Regards,

Bader

Answer 1

No, you cannot with CTE, add the result to temporary table  and create a PK on that temporary table

CREATE TABLE #tmp(SerID INT NOT NULL PRIMARY KEY, col1...............)

Declare @TableName nvarchar(50) 
Set @TableName = 'Quz' 
Declare @TargetTableName nvarchar(50) 
Set @TargetTableName = 'Osn' 
DEclare @SerID bigint 
Set @SerID = 427 

Exec(N' 
	WITH ret AS( 
		SELECT * FROM guides' + @TableName + N' 
		WHERE SerID = ' + @SerID + N' 
		UNION ALL 
		SELECT t.* FROM guides' + @TableName + N' t INNER JOIN ret r ON t.ParentID = r.SerID 
	)
	
	INSERT INTO #tmp SELECT * from ret;SELECT * FROM #tmp
')

 

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Hi,

Thank you for your reply, the first problem is solved.

 

Please I need your help in order to solve the second problem from my first post.

 

Regards,

Bader

Answer 3

Nadim

please try this

 

Declare @TableName nvarchar(50) 
Set @TableName = 'Quz' 
DEclare @SerID bigint 
declare @type int
Set @SerID = 427 
DECLARE @ParmDefinition nvarchar(500);
declare @sql  nvarchar(1000)='select @Typeout = (Select [Type] from ' + @TableName + N'  where [SerID] = ' + @SerID + ')'
SET @ParmDefinition = N' @TypeOut int OUTPUT';

sp_executesql @sql,@ParmDefinition,@Typeout=@type OUTPUT

select @type

VT

---

Please mark answered if I've answered your question and vote for it as helpful to help other user's find a solution quicker

Answer 4

Hey Nadim,

I guess VT query slove your problem,

Modified VT query(not mine),Try this as well

Declare

@TableName nvarchar(50)

Set

@TableName = 'Quiz'

DEclare

@SerID bigint

declare

@type

int

declare

@sql nvarchar(1000

)

Set

@SerID = 427

DECLARE

@ParmDefinition nvarchar(500

);

SET

@sql='select @Typeout = (Select [Type] from ' + @TableName + N' where [SerID] =('+Convert(Nvarchar(100), @SerID)+

'))'

SELECT

@sql

SET

@ParmDefinition = N' @TypeOut int OUTPUT'

;

EXEC

sp_executesql @sql,@ParmDefinition,@Typeout=@type

OUTPUT

select

@type

 

---

SNIVAS

Answer 5

Hi,

 

Thank you and VT for your reply, I tried you code with some changes,

Declare @TableName nvarchar(50) 
Set @TableName = 'Quz' 
declare @type nchar(10) 
declare @sql nvarchar(1000)

DECLARE @ParmDefinition nchar(10);
SET @sql='select @TypeOut = (Select [Type] from ' + @TableName + N' where [SerID] = 427)'
SELECT @sql
SET @ParmDefinition = N' @TypeOut nchar(10) OUTPUT';
EXEC sp_executesql @sql,@ParmDefinition,@TypeOut=@type OUTPUT 
select @type
 
 

 

but I get the following error:

 

(1 row(s) affected)
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')'.
Msg 137, Level 15, State 1, Line 1
Must declare the scalar variable "@TypeOut".

(1 row(s) affected)

 

Please, I need your help,

 

Regards,

Bader