question

AnthonyC-4036 avatar image
0 Votes"
AnthonyC-4036 asked EchoLiu-msft commented

How to return a compiled string from a SPROC

Good Afternoon Everyone

I am working with the scenario of transferring client data from one database to another, this is something that is done very rarely here.

So I've decided to create a stored proc that processes each table in turn, the table name is passed to a SPROC I have called "dbo.BMSClientDataTransferGetColumnNames" and it is supposed to return a SQL Command based on Identity insert, exclude computed columns.

The Sproc code is as follows: -

ALTER PROCEDURE [dbo].[BMSClientDataTransferGetColumnNames]
@Destination VarChar(500),
@Source VarChar(500),
@Table VarChar(500),
@WithWhere Char(1)='N',
@WhereClause VarChar(125)=NULL,
@SQLCommandOutput VarChar(max) OUTPUT
AS
BEGIN
DECLARE
@Check tinyint,
@IdentityCheck tinyint,
@ColumnList VarChar(3000),
@ColumnName VarChar(255),
@StringLen int,
@TableName VarChar(255)
SET @Check =(SELECT COUNT() FROM sys.tables WHERE [name]=@Table)
IF @Check=0
BEGIN
SET @SQLCommandOutput=(SELECT'SELECT ['+@Table+'] is not a table, check the name of the Table')
GOTO EndProcess
END
SELECT c.name,c.is_identity,c.is_computed INTO #Columns FROM sys.columns c INNER JOIN sys.tables t ON t.principal_id=c.object_id WHERE t.[name]=@Table
SELECT @IdentityCheck =(SELECT COUNT(
) from #Columns WHERE is_identity=1)
DECLARE Build_CURS CURSOR FOR SELECT name FROM #Columns WHERE is_computed = 0
OPEN Build_CURS
FETCH NEXT FROM Build_CURS INTO @ColumnName
WHILE @@FETCH_STATUS=0
BEGIN
SET @ColumnList=(SELECT @ColumnList+@ColumnName+',')
FETCH NEXT FROM Build_CURS INTO @ColumnName
END
CLOSE Build_CURS
DEALLOCATE Build_CURS
SET @StringLen =LEN(@ColumnList)-1
SET @ColumnList=LEFT(@ColumnList,@StringLen)


SET @TableName=(SELECT '['+SCHEMA_NAME(schema_id)+'].['+name+']' AS SchemaTable FROM sys.tables WHERE name=@Table)
SET @SQLCommandOutput=(SELECT 'INSERT INTO '+@Destination+'.'+@TableName+'
(
'+@ColumnList+'
)
SELECT
'+@ColumnList+'
FROM '+@Source + '.'+@TableName)
IF @WithWhere='Y' SET @SQLCommandOutput=(SELECT @SQLCommandOutput + ' '+@WhereClause)
IF @IdentityCheck>0
BEGIN
SET @SQLCommandOutput =(SELECT 'SET IDENTITY_INSERT '+@TableName+' ON;
'+@SQLCommandOutput+'
SET IDENTITY_INSERT '+@TableName+' OFF;')
END
EndProcess:
SELECT @SQLCommandOutput RETURN
END

And I'm executing as follows

declare @SQLCommand nvarchar(max)
EXEC @SQLCommand=dbo.BMSClientDataTransferGetColumnNames

@Destination='DestinationDB',
@Source ='[ServerName].[SourceDB]',
@Table='PermissionsProfiles',
@WithWhere='Y',
@WhereClause='WHERE ID NOT IN (SELECT ID FROM dbo.[PermissionsProfiles])',
@SQLCommandOutput=@SQLCommand OUTPUT

SELECT @SQLCommand

However when executed only NULL values are being returned

sql-server-generalsql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Have you tried the following methods? Could you have any updates?

Echo

0 Votes 0 ·
OlafHelper-2800 avatar image
0 Votes"
OlafHelper-2800 answered

EXEC @SQLCommand =dbo.BMSClientDataTransferGetColumnNames
However when executed only NULL values are being returned

The execute result as here is an integer scalar value returned by the SP with the last RETURN command; your RETURN returns NULL.

You have to take the result of OUTPUT parameter @SQLCommandOutput the right way, see CREATE PROCEDURE (Transact-SQL) => Example F. Using OUTPUT parameters



5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Hi @AnthonyC-4036

There seems to be some problems with the statement that you call the stored procedure.
Please try:

 declare @SQLCommand nvarchar(max)
 EXEC [dbo].[BMSClientDataTransferGetColumnNames]
    
 @Destination='DestinationDB',
 @Source ='[ServerName].[SourceDB]',
 @Table='PermissionsProfiles',
 @WithWhere='Y',
 @WhereClause='WHERE ID NOT IN (SELECT ID FROM dbo.[PermissionsProfiles])',
 @SQLCommand OUTPUT
    
 SELECT @SQLCommand

If you have any question, please feel free to let me know.


Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.