September 29, 2011 at 4:55 pm
This is what I currently have as my datastring and i am binding it to the datagrid at runtime.
DataString = "SELECT UserID, CreatedOnDate, Username, FirstName, LastName, Email, PokerRegion, Street, State, PostalCode, City, DOB FROM vwFullPlayerInfo WHERE (UserID IN (SELECT userid FROM PPMSPoints WHERE " & Venue & "(matchdate BETWEEN '" & HCtxtFrom & "' AND '" & HCtxtTo & "'))) ORDER BY FirstName"
This works fine, but now i need to add 2 more columns from a stored procedure to the datagrid. I am lost as to how i can add this last part. I need to pull finalplace and totalpoints from the below stored procedure based on the same userid, venue, and dates as above. I am not sure how to join it to the original query.
Here is the stored procedure. I could really use some help on this one.
USE [riverchasers]
GO
/****** Object: StoredProcedure [dbo].[PPMSPlayerReport] Script Date: 09/26/2011 21:24:02 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[PPMSPlayerReport]
@portalid int,
@fromdate datetime,
@todate datetime,
@fullname varchar(100),
@venueid int,
@regionid int
AS
DECLARE @sql NVARCHAR(4000)
DECLARE @SQL1 NVARCHAR(1000)
DECLARE @SQL2 NVARCHAR(1000)
DECLARE @SQL3 NVARCHAR(1000)
DECLARE @SQL4 NVARCHAR(1000)
DECLARE @SQLVenue NVARCHAR(500)
SET @SQL1 = ''
SET @SQL2 = ''
SET @SQL3 = ''
SET @SQL4 = ''
SET @SQLVenue = ''
CREATE TABLE #temp (rank int IDENTITY (1, 1) ,userid int, fullname nvarchar(150), firstplace int, secondplace int, thirdplace int, finalplace int, totalpoints int)
--determines if need to add PPMSVenue
IF @venueid IS NOT NULL OR @regionid IS NOT NULL
SET @SQLVenue = ', dbo.PPMSVenue V1 '
SET @sql = ''
SET @sql = @sql + 'INSERT INTO #temp (userid,fullname, firstplace, secondplace, thirdplace, finalplace, totalpoints) '
SET @sql = @sql + 'P.Userid, U.firstname + '' '' + U.lastname AS fullname, '
SET @SQL1 = @SQL1 + '(select count(*) FROM dbo.PPMSPoints P1 ' + @SQLVenue + ' WHERE P1.userid = P.userid AND P1.position = 1 AND P1.matchdate between ''' + CAST(@fromdate AS nvarchar(50)) + ''' AND ''' + CAST(@todate AS NVARCHAR(50)) + ''''
SET @SQL2 = @SQL2 + '(select count(*) FROM dbo.PPMSPoints P2 ' + @SQLVenue + ' WHERE P2.userid = P.userid AND P2.position = 2 AND P2.matchdate between ''' + CAST(@fromdate AS nvarchar(50)) + ''' AND ''' + CAST(@todate AS NVARCHAR(50)) + ''''
SET @SQL3 = @SQL3 + '(select count(*) FROM dbo.PPMSPoints P3 ' + @SQLVenue + ' WHERE P3.userid = P.userid AND P3.position = 3 AND P3.matchdate between ''' + CAST(@fromdate AS nvarchar(50)) + ''' AND ''' + CAST(@todate AS NVARCHAR(50)) + ''''
SET @SQL4 = @SQL4 + '(select count(*) FROM dbo.PPMSPoints P4 ' + @SQLVenue + ' WHERE P4.userid = P.userid AND P4.position >= 1 AND P4.position <= 8 AND P4.matchdate between ''' + CAST(@fromdate AS nvarchar(50)) + ''' AND ''' + CAST(@todate AS NVARCHAR(50)) + ''''
IF @venueid IS NOT NULL OR @regionid IS NOT NULL
BEGIN
SET @SQL1 = @SQL1 + ' AND V1.venueid = P1.venueid '
SET @SQL2 = @SQL2 + ' AND V1.venueid = P2.venueid '
SET @SQL3 = @SQL3 + ' AND V1.venueid = P3.venueid '
SET @SQL4 = @SQL4 + ' AND V1.venueid = P4.venueid '
END
IF @venueid IS NOT NULL
BEGIN
SET @SQL1 = @SQL1 + 'AND V1.venueid = ' + CAST(@venueid AS VARCHAR(5)) + ' '
SET @SQL2 = @SQL2 + 'AND V1.venueid = ' + CAST(@venueid AS VARCHAR(5)) + ' '
SET @SQL3 = @SQL3 + 'AND V1.venueid = ' + CAST(@venueid AS VARCHAR(5)) + ' '
SET @SQL4 = @SQL4 + 'AND V1.venueid = ' + CAST(@venueid AS VARCHAR(5)) + ' '
END
IF @regionid IS NOT NULL
BEGIN
SET @SQL1 = @SQL1 + 'AND V1.regionid = ' + CAST(@regionid AS VARCHAR(5)) + ' '
SET @SQL2 = @SQL2 + 'AND V1.regionid = ' + CAST(@regionid AS VARCHAR(5)) + ' '
SET @SQL3 = @SQL3 + 'AND V1.regionid = ' + CAST(@regionid AS VARCHAR(5)) + ' '
SET @SQL4 = @SQL4 + 'AND V1.regionid = ' + CAST(@regionid AS VARCHAR(5)) + ' '
END
SET @SQL1 = @SQL1 + ' ) AS firstplace, '
SET @SQL2 = @SQL2 + ' ) As secondplace, '
SET @SQL3 = @SQL3 + ' ) AS thirdplace, '
SET @SQL4 = @SQL4 + ' ) AS finalplace, '
--PRINT @SQL1
SET @sql = @sql + @SQL1 + @SQL2 + @SQL3 + @SQL4
SET @sql = @sql + 'SUM(P.points) totalpoints '
SET @sql = @sql + 'FROM dbo.PPMSPoints P, dbo.Users U '
IF @venueid IS NOT NULL OR @regionid IS NOT NULL
SET @sql = @sql + ', dbo.PPMSVenue V '
SET @sql = @sql + 'P.portalid = ' + CAST(@portalid AS VARCHAR(5)) + ' '
SET @sql = @sql + 'AND P.userid = U.userid '
SET @sql = @sql + 'AND P.matchdate between ''' + CAST(@fromdate AS nvarchar(50)) + ''' AND ''' + CAST(@todate AS NVARCHAR(50)) + ''' '
IF @fullname <> ''
SET @sql = @sql + 'AND upper(U.firstname + '' '' + U.lastname) like ''%' + upper(@fullname) + '%'' '
IF @venueid IS NOT NULL OR @regionid IS NOT NULL
SET @sql = @sql + 'AND V.venueid = P.venueid '
IF @venueid IS NOT NULL
SET @sql = @sql + 'AND V.venueid = ' + CAST(@venueid AS VARCHAR(5)) + ' '
IF @regionid IS NOT NULL
SET @sql = @sql + 'AND V.regionid = ' + CAST(@regionid AS VARCHAR(5)) + ' '
SET @sql = @sql + ' P.userid, U.firstname, U.lastname '
SET @sql = @sql + 'ORDER BY 7 desc, 3 desc, 4 desc, 5 desc, 6 desc '
--PRINT @sql
EXEC SP_EXECUTESQL @sql
SELECT * FROM #temp ORDER BY rank
September 30, 2011 at 8:45 am
What programming language are you using? Is this a .net application? If it is and you have a key that matches between your two result sets. You can return your two result sets into a dataset, then merge the dataset and you will have your two new columns. Then bind the datagrid to the merged dataset.
Hope that helps.
September 30, 2011 at 9:26 am
Too much dynamic sql and no comments to tell us what is going on.
It would help if there was something that showed what SQL was being built so that it would be easier to understand the logic.
September 30, 2011 at 9:41 am
Yes it is .net. This sounds like it may be a viable option. Not sure exactly how to do that but i believe i can find the info for this on Google. Thanks for your help, i will give it a try.
September 30, 2011 at 9:45 am
While you're off on Google, may I suggest you read up on SQL Injection. Your code is vulnerable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2011 at 9:54 am
The stored procedure is actually not my code. I have recently taken over this project and website and we are rebuilding a lot of this. The stored procedure is being called from a module and has no user input whatsoever.
September 30, 2011 at 9:56 am
I wasn't talking about the stored procedure.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 30, 2011 at 10:00 am
If you mean the query then that is pretty much the same with the variables used within the query being pulled from another part of the DB and stored in DropDownLists that are readonly.
September 30, 2011 at 1:37 pm
I have spent the last few hours trying to get that to work. It should be fairly simple yet it will not work for some reason. Would it be easier to add the query into the stored procedure code and then use that to create a 2nd stored procedure?
September 30, 2011 at 2:55 pm
mike 69307 (9/30/2011)
I have spent the last few hours trying to get that to work. It should be fairly simple yet it will not work for some reason. Would it be easier to add the query into the stored procedure code and then use that to create a 2nd stored procedure?
You best bet would be to change your top code (in line sql) to be a stored proc. In that stored proc you could have your second stored proc return its data into a table variable then you could add a join to that table variable and return the data.
So if your existin stored proc returns three columns you need to define a table variable that matches the output:
declare @Tmp table (id int identity(1,1) primary key, userID int, total1 numeric(18,6), total2 numeric(18,6))
insert into @Tmp
exec yourstoredprocnamehere
Then join to @Tmp on your userID and return all the columns needed.
Hope that helps.
September 30, 2011 at 5:26 pm
This sounds like what i was thinking. I am new to SP as most of the coding I have done was using fairly simple queries. I think i have the gest of what your saying and will give it a go, hopefully i can figure it out. I need to get this finished as this is all that is holding me up. Thanks for your help.
October 1, 2011 at 12:48 pm
I have been working on this and have some of it completed but am unable to join the stored procedure. The first part of this code is my original query, that worked and when executed returned the data. Now in the second part I am trying to execute the SP and insert in the table, then do the join as you suggested. I am lost on that part. Here is what I have so far.
USE [riverchasers]
GO
/****** Object: StoredProcedure [dbo].[hc_fullrankingsexport] Script Date: 10/01/2011 13:24:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[hc_fullrankingsexport]
-- Add the parameters for the stored procedure here
@portalid int,
@venueid int = null,
@fromdate datetime,
@todate datetime,
@regionid int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
if @venueid IS NULL
SELECT UserID, Username, FirstName, LastName, Email, PokerRegion, Street, State, PostalCode, City FROM vwFullPlayerInfo WHERE (UserID IN (SELECT userid FROM PPMSPoints WHERE (matchdate BETWEEN @fromdate AND @todate ))) ORDER BY FirstName
IF @venueid IS NOT NULL
SELECT UserID, Username, FirstName, LastName, Email, PokerRegion, Street, State, PostalCode, City FROM vwFullPlayerInfo WHERE (UserID IN (SELECT userid FROM PPMSPoints WHERE venueid = @venueid AND (matchdate BETWEEN @fromdate AND @todate ))) ORDER BY FirstName
END
BEGIN
declare @Tmp table (userid int, finalplace int, totalpoints int)
insert into @Tmp (userid, finalplace, totalpoints)
SELECT @portalid UNION ALL
SELECT @venueid
SELECT @fromdate
SELECT @todate
SELECT @regionid
RETURN @Tmp
END
October 1, 2011 at 1:27 pm
declare @Tmp table (userid int, finalplace int, totalpoints int)
insert into @Tmp (userid, finalplace, totalpoints)
SELECT @portalid UNION ALL
SELECT @venueid
SELECT @fromdate
SELECT @todate
SELECT @regionid
What are you trying to do here? You've defined a 3-column wide table and are trying to insert 2 rows with only one column. It's going to throw an error. Number of columns in the select must match number of column in the insert (and if you're inserting values, use the INSERT INTO ... VALUES form of insert)
What are those other three SELECTs for?
RETURN @Tmp
A stored procedure's return code must be an integer, and by convention it's a number indicating success or failure (like DOS application return codes). The return statement isn't necessary to have, if it's omitted the procedure implicitly returns 0.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 1, 2011 at 4:24 pm
mike 69307 (10/1/2011)
I have been working on this and have some of it completed but am unable to join the stored procedure. The first part of this code is my original query, that worked and when executed returned the data. Now in the second part I am trying to execute the SP and insert in the table, then do the join as you suggested. I am lost on that part. Here is what I have so far.USE [riverchasers]
GO
/****** Object: StoredProcedure [dbo].[hc_fullrankingsexport] Script Date: 10/01/2011 13:24:13 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[hc_fullrankingsexport]
-- Add the parameters for the stored procedure here
@portalid int,
@venueid int = null,
@fromdate datetime,
@todate datetime,
@regionid int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
if @venueid IS NULL
SELECT UserID, Username, FirstName, LastName, Email, PokerRegion, Street, State, PostalCode, City FROM vwFullPlayerInfo WHERE (UserID IN (SELECT userid FROM PPMSPoints WHERE (matchdate BETWEEN @fromdate AND @todate ))) ORDER BY FirstName
IF @venueid IS NOT NULL
SELECT UserID, Username, FirstName, LastName, Email, PokerRegion, Street, State, PostalCode, City FROM vwFullPlayerInfo WHERE (UserID IN (SELECT userid FROM PPMSPoints WHERE venueid = @venueid AND (matchdate BETWEEN @fromdate AND @todate ))) ORDER BY FirstName
END
BEGIN
declare @Tmp table (userid int, finalplace int, totalpoints int)
insert into @Tmp (userid, finalplace, totalpoints)
SELECT @portalid UNION ALL
SELECT @venueid
SELECT @fromdate
SELECT @todate
SELECT @regionid
RETURN @Tmp
END
It needs to look like this:
declare @Tmp table (rank int ,userid int, fullname nvarchar(150), firstplace int, secondplace int, thirdplace int, finalplace int, totalpoints int)
At the top of your new stored proc:
insert into @Tmp
exec ppmsplayerReport @portalid = @portalid, @fromdate = @fromdate, @todate = @todate,
@venueid = @venueid, @regionid = @regionid, @fullname = ?????
I think you need to pass fullname into the new proc you created.
Now join to the @Tmp table you just created:
...
,t.finalplace , t.totalpoints
FROM vwFullPlayerInfo fpi
join @Tmp t on fpi.userid = t.userid
Don't forget to add to all your select statements.
Hope that helps.
Ben
October 1, 2011 at 4:46 pm
So i need to bring the create table to the top above the select statements, and then add this to the select statements?
t.finalplace , t.totalpoints
FROM vwFullPlayerInfo fpi
join @Tmp t on fpi.userid = t.userid
is that what you are saying? How would i add it to the select statements? BTW, thanks for your help, it is much appreciated!
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply