May 26, 2018 at 4:10 am
Hi experts,
How can i use Dynamic SQL with Inner join and Pickup in the Select
Please see my below code
declare @SQLGCLMNLCheckEmailDetails as nvarchar(max),@EmailAddress nvarchar(50),@CheckMNLGCPStatus as varchar(50),@checkUserMNLGCP as int,@MNLGCPIDNO as varchar(50)
set @EmailAddress='freelancer0320@gmail.com'
set @SQLGCLMNLCheckEmailDetails=' select '+@CheckMNLGCPStatus+'=A.status,
'+@checkUserMNLGCP+'=count(A.EMAIL),
'+@MNLGCPIDNO+'=D.trid
from hvdc01.gcp.dbo.tblaccount A
inner join hvdc01.gcp.dbo.tblAccntTransaction B
on A.IDNO=B.IDNO
inner join hvdc01.gcp.dbo.tblFianceeTemp C
--on C.IDNO=B.TRID
on C.EMEMADDR=A.EMAIL
inner join hvdc01.gcp.dbo.tblBarcode D
on D.TRID=C.IDNO
where A.EMAIL='+@EmailAddress+'
group by A.status ,d.trid'
exec (@SQLGCLMNLCheckEmailDetails);
Hope you can help me.
Thanks and Best Regards
May 26, 2018 at 4:28 am
Can you provide sample tables and data on a consumable format as well as desired output.
although I think I know what you are after the above will not work if I am correct so I do not wish to make assumptions.
On another note you should review your code as using 4 naming convention is not advisable (neither is 3). For that purpose you should use a mix of synonyms/views.
May 29, 2018 at 10:10 pm
Please also print the value of @SQLGCLMNLCheckEmailDetails, not just do an exec(). Look at the code and see if it looks correct. If it doesnt' execute, then give us the error and we can help.
May 30, 2018 at 2:28 am
I suggest against string concatenation like that as well, for your dynamic SQL.Personally, i suggest using QUOTENAME and checking for the existence of the column(s) in sys.columns. As for @EmailAddress, you should be using a parametrised SQL for that.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 30, 2018 at 3:12 am
Ok, firstly, an explanation of what i've done below.
Firstly, I've added the PRINT statement that Steve mentioned; aka your best friend. Printing the value of your dynamic SQL is incredibly useful, as you can copy the code that was run and debug that first.
Next, one of my biggest pet peeves's, your aliasing... "A" for account, good, but "D" is for Barcode and "B" is for "AccntTransaction"? Doesn't Barcode begin with a "B"? Use good, and consistent aliasing; using things like A, B, C, D, or T1, T2, T3, etc is just asking for problems. What happens if you change the order of the tables, let's say you put FinanceeTemp as your 4th object and Barcode as your 3rd, do you go through as re-alias everything in your SELECT because "C" is for Barcode now? It doesn't make sense. Aaron Bertrand did a great article on this in his Bad Habits to Kick articles.
Next, I've changed the way you've aliased your columns. You had the line '+@checkUserMNLGCP+'=count(A.EMAIL),, however, @checkUserMNLGCP is an int. On it's own (a numeric data type) + (any non-numeric string) = error; this is most likely why your dynamic SQL is failing. Even, however, if you were to CAST/CONVERT your int value, it would still fail; you can't start a column's name with a numeric without quoting it. Try these:SELECT 1 = 'a'; --errors
GO
SELECT 'a' AS 1; --errors
GO
SELECT 1a = 'a'; --errors
GO
SELECT 'a' AS 1a; --errors
GO
SELECT [1] = 'a'; --Good!
GO
SELECT 'a' AS [1]; --Good!
GO
SELECT a1 = 'a'; --Good!
GO
SELECT 'a' AS a1; --Good!
In my solution, however, I've used the AS syntax, rather that the [name] = [value] syntax. It you're using a numeric value for a column I think that something like [1] = COUNT(A.EMAIL) would seem very confusing.
You'll also notice I've parametrised the SQL properly; I've not injected the value of @EmailAddress into the dynamic SQL. Then, instead, I use sp_executesql to pass the value of @EmailAddress to the parameter @dEmailAddress in the dynamic SQL. This gives the final result below:
Of course, I can't test the above, so you'll need to test it. Make sure you understand it though, if you don't, them ask about it.
P.s. I still hate pasting into SSC, and hence why i didn't use the Code IFMarkup for the main code, as all my formatting would be lost and it's impossible to fix if you don't use a font that's fixed width.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 30, 2018 at 5:29 am
OP seems to be gone.
I didn't post any idea or sugestion as I believe that what the OP is trying to achive is to execute the sql and return the values of
- A.status - on variable @CheckMNLGCPStatus
- count(*a.email) - on variable @checkUserMNLGCP (thus it being a int)
- d.trid - on variable @MNLGCPIDNO
for the records that match the email supplied
instead of those variables meaning the column names that should be returned from the query.
Not even taking in consideration that fact that with dynamic sql it can't be built exactly like that, unless the query always returns a single row it won't work as is.
so really no dynamic sql but rather
declare @table table
(CheckMNLGCPStatus varchar(50)
, checkUserMNLGCP int
, MNLGCPIDNO varchar(50)
)
insert into @table
select A.status
, count(A.EMAIL)
, D.trid
from hvdc01.gcp.dbo.tblaccount A
inner join hvdc01.gcp.dbo.tblAccntTransaction B
on A.IDNO=B.IDNO
inner join hvdc01.gcp.dbo.tblFianceeTemp C
--on C.IDNO=B.TRID
on C.EMEMADDR=A.EMAIL
inner join hvdc01.gcp.dbo.tblBarcode D
on D.TRID=C.IDNO
where A.EMAIL = @EmailAddress
group by A.status
, d.trid
-- we could return a single row on the variables but if there is more than 1 record it would be probably incorrect to do so
select top 1
@CheckMNLGCPStatus = CheckMNLGCPStatus
, @checkUserMNLGCP = checkUserMNLGCP
, @MNLGCPIDNO = MNLGCPIDNO
from @table
May 30, 2018 at 8:44 am
frederico_fonseca - Wednesday, May 30, 2018 5:29 AMOP seems to be gone.I didn't post any idea or sugestion as I believe that what the OP is trying to achive is to execute the sql and return the values of
- A.status - on variable @CheckMNLGCPStatus
- count(*a.email) - on variable @checkUserMNLGCP (thus it being a int)
- d.trid - on variable @MNLGCPIDNOfor the records that match the email supplied
instead of those variables meaning the column names that should be returned from the query.
Not even taking in consideration that fact that with dynamic sql it can't be built exactly like that, unless the query always returns a single row it won't work as is.
so really no dynamic sql but rather
select TOP 1
@CheckMNLGCPStatus = A.status
, @checkUserMNLGCP = count(A.EMAIL)
, @MNLGCPIDNO = D.trid
from hvdc01.gcp.dbo.tblaccount A
inner join hvdc01.gcp.dbo.tblAccntTransaction B
on A.IDNO=B.IDNO
inner join hvdc01.gcp.dbo.tblFianceeTemp C
--on C.IDNO=B.TRID
on C.EMEMADDR=A.EMAIL
inner join hvdc01.gcp.dbo.tblBarcode D
on D.TRID=C.IDNO
where A.EMAIL = @EmailAddress
group by A.status
, d.trid
I would remove the table variable and leave a single statement.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply