February 10, 2020 at 5:26 pm
I am trying to create a bit of dynamic sql to select from a table with criteria for FirstRow and LastRow
Now if these are specified then I would write the following
DECLARE @pFirstRow INT = NULL
DECLARE @pLastRow INT = NULL
SELECT RowNumber,
DuplicateRowNumber,
PartyURN,
FirstName,
LastName,
EmailAddress,
AddressLine1,
AddressLine2,
AddressLine3,
AddressLine4,
AddressLine5,
PostCode,
PayrollRef,
NINumber,
NotificationMethod,
NULL,
NULL,
Corporate
FROM PushPortfolioData_7AC7FC1A3FF54954BF0BBF741F71704B
WHERE rowNumber BETWEEN COALESCE(@pFirstRow, rowNumber) AND COALESCE(@pFirstRow, rowNumber)
When I try to write this as follows in Dynamic SQL
DECLARE
@pDataTableName varchar(1000) = 'PushPortfolioData_7AC7FC1A3FF54954BF0BBF741F71704B',
@pFirstRow Int = NULL
@pLastRow INT = NULL
declare @sql nvarchar(max)
set @sql = 'select RowNumber, DuplicateRowNumber, PartyURN, FirstName, LastName, EmailAddress, AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, PostCode, PayrollRef, NINumber, NotificationMethod, null, null, Corporate ' +
'from ' + Cast(@pDataTableName as varchar(max)) +
' where rowNumber Between COALESCE(' +
+ @pFirstRow + ',RowNumber)' + 'AND COALESCE(' + @pLastRow + ',rowNumber' + ')'
print (@sql)
I get the following error message
Msg 245, Level 16, State 1, Line 25
Conversion failed when converting the varchar value 'select RowNumber, DuplicateRowNumber, PartyURN, FirstName, LastName, EmailAddress, AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, PostCode, PayrollRef, NINumber, NotificationMethod, null, null, Corporate from PushPortfolioData_7AC7FC1A3FF54954BF0BBF741F71704B where rowNumber Between COALESCE(' to data type int.
What am I doing wrong here?
February 10, 2020 at 5:41 pm
I think, but I'm not sure, it's because @pFirstRow and @pLastRow are ints. The compiler sees these int values and the '+' signs and tries to convert the strings to ints so it can add them together.
I think if you use + cast(@pFirstRow as varchar) and +cast(@pLastRow as varchar) it might work.
It would be useful if you could provide test data as described here https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help. It makes it easier for people to help you and provide solutions they are sure will work.
February 10, 2020 at 5:56 pm
Thank you SSCrazy
The other problem I have is that if I pass in NULL Values for @pFirstRow and @pLastRow , then the whole expression gets evaluated to empty!
February 10, 2020 at 6:04 pm
I'm not sure about the null issue. Using convert rather than cast might help but I'm not sure.
Can you post some test data? It would really increase the chances of someone being able to help you find a solution. If it's confidential then you can change the values.
February 10, 2020 at 7:20 pm
If you pass NULL, then the whole string gets NULLed out because any string concatenated with NULL is NULL. You'll need to use an ISNULL in your SQL to convert it to an empty string or other value you want to use.
February 11, 2020 at 12:06 pm
OK here is some test data and what I want to do
-- DROP TEMP TABLE IF ALREADY EXISTS
If OBJECT_ID(N'tempdb..#OutputTable', N'U') IS NOT NULL DROP TABLE #OutputTable
CREATE TABLE [#OutputTable](
[FirstName] [varchar](255) NULL,
[LastName] [varchar](255) NULL,
[RowNumber] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL
) ON [PRIMARY]
GO
INSERT [#OutputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'JOE', N'BLOGGS', 123)
GO
INSERT [#OutputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'OJ', N'SMITH', 4541)
GO
INSERT [#OutputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'JOHN', N'HART', 4545)
GO
INSERT [#OutputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'JENIFER', N'HART', 445)
GO
INSERT [#OutputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'DAWN', N'LEE', 4567)
GO
INSERT [#OutputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'PHIL', N'REYNOLDS', 2332)
GO
INSERT [#OutputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'ANDY', N'FARLEY', 1255453)
GO
Then I want to be able to dynamically query the table above as follows
declare @sql nvarchar(max)
DECLARE
@pDataTableName varchar(1000) = '#OutputTable',
@pFirstRow Int = NULL,
@pLastRow INT = NULL
set @sql =
--'insert into #PushPortfolioData ' +
'select [FirstName], [LastName],[UserID], [RowNumber] ' +
'from ' + @pDataTableName +
' where [RowNumber] Between ' +
'COALESCE(Cast(' + @pFirstRow + 'as VARCHAR(6)),rownumber)'
'and '
'COALESCE(Cast(' + @pFirstRow + 'as VARCHAR(6)) + ',rowNumber)'
So I want pass in values for @pFirstRow and @pLastRow or as NULL to return the whole table
February 11, 2020 at 2:37 pm
Hi All
I found a solution to this
What you need to do is to cast the variables @pFirstRow and @pLastRow to varchar variables and then
handle the nullable value
For example
DECLARE
@pDataTableName varchar(1000) = 'pp_3CC5BBC5B3F14F30A5FE1A9A4A6779C6',
@pFirstRow Int = NULL,
@pLastRow INT = NULL,
@strFirstRow VARCHAR(20),
@strLastRow VARCHAR(20)
select @strFirstRow = IsNull(Cast(@pFirstRow as varchar(20)),'[RowNumber]')
select @strLastRow = IsNull(Cast(@pLastRow as varchar(20)),'[RowNumber]')
So the case where @pFirstRow and @pLastRow are null, then
strLastRow = '[RowNumber]' and @strLastRow ='[RowNumber]'
Then my where clause gets written as 'from [#OutputTable] where [RowNumber] Between [RowNumber] And [RowNumber]'
from PushPortfolioData_3CC5BBC5B3F14F30A5FE1A9A4A6779C6 where [RowNumber] Between [RowNumber] And [RowNumber]
February 11, 2020 at 2:47 pm
Thanks for the test data. The following should work. I tested it with a few combinations with no errors.
drop table #outputTable
CREATE TABLE [#outputTable](
[FirstName] [varchar](255) NULL,
[LastName] [varchar](255) NULL,
[RowNumber] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL
) ON [PRIMARY]
GO
INSERT [#outputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'JOE', N'BLOGGS', 123)
GO
INSERT [#outputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'OJ', N'SMITH', 4541)
GO
INSERT [#outputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'JOHN', N'HART', 4545)
GO
INSERT [#outputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'JENIFER', N'HART', 445)
GO
INSERT [#outputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'DAWN', N'LEE', 4567)
GO
INSERT [#outputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'PHIL', N'REYNOLDS', 2332)
GO
INSERT [#outputTable] ( [FirstName], [LastName],[UserID]) VALUES ( N'ANDY', N'FARLEY', 1255453)
GO
declare @sql nvarchar(max),@pDataTableName varchar(1000) = '#outputTable',
@pFirstRow Int = null, @pLastRow INT = null
set @sql =
--'insert into #PushPortfolioData ' +
'select [FirstName], [LastName],[UserID], [RowNumber] '
+ 'from ' + @pDataTableName
+
' where [RowNumber] Between ' +
'COALESCE(' + isnull(cast(@pFirstRow as varchar),'null') + ',rownumber) ' +
'and ' +
'COALESCE(' + isnull(cast(@pLastRow as varchar),'null') + ' ,rowNumber)'
print ( @sql)
The changes I made were as follows:
I moved the cast operations outside of the string quotes. If it's inside the string quotes then the system won't execute it so it is still trying to add a string to a number.
Added the isnull function. Replacing actual null with a 'null' string is perhaps a workaround, someone might have a better idea but it seems to work.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply