Please start any new threads on our new site at http://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 2 updates within 1 query window...
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cowski
Starting Member

USA
30 Posts

Posted - 07/17/2013 :  08:03:13  Show Profile  Send Cowski an ICQ Message  Reply with Quote
I have 2 dropdown parameters on my report. We'll call them "Update Production(Y/N)" and "Update AT(Y/N)".
Is it possible, within 1 query window, on a dataset property to have a logic in there that if the parameter is 1 (Yes) from the "Update Production(Y/N)" dropdown, update the Production table with values. And if the parameter is 1 (Yes) from the "Update AT(Y/N)" dropdown, update the AT table? Then after the updates happen (or not happen), a select statement runs to repopulate the report with the updated values.
Below is the code I'm wanting to see work within 1 query window. If this is not possible within 1 query window, what can I do to make this work:


If @UpdateProduction = 1 
    BEGIN
	   Update Orders.ProductVendorControl
	   SET ConfirmedProdDate = GETDATE(),
	   ConfirmedProdBy = @UserId
	   Where VendorId = @VendorIdLookup
    END

  
If @UpdateAT = 1
    BEGIN
	   Update Orders.ProductVendorControl
	   SET ConfirmedATDate = GETDATE(),
	   ConfirmedATBy = @UserId
	   Where VendorId = @VendorIdLookup
    END  

Select * from tables...


Thank you for your time.

Edited by - Cowski on 07/17/2013 08:03:58

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/17/2013 :  11:11:59  Show Profile  Reply with Quote
you can use the query in single query window. Didnt understand what was the issue you faced?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Cowski
Starting Member

USA
30 Posts

Posted - 07/17/2013 :  11:44:46  Show Profile  Send Cowski an ICQ Message  Reply with Quote
I was getting errors whenever I'd throw the 2nd IF statement in there.
"Incorrect syntax near ','" error. Fixed that by implementing a "in ( )" with the where clause. Fixed that right up.

So note to self....if you get the error "Incorrect syntax near ','" fix it by implementing a "in ( )" with the where clause.

Thanks!!

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/18/2013 :  01:36:27  Show Profile  Reply with Quote
quote:
Originally posted by Cowski

I was getting errors whenever I'd throw the 2nd IF statement in there.
"Incorrect syntax near ','" error. Fixed that by implementing a "in ( )" with the where clause. Fixed that right up.

So note to self....if you get the error "Incorrect syntax near ','" fix it by implementing a "in ( )" with the where clause.

Thanks!!




hmm..was it a delimited list that you were trying to pass in WHERE? If yes, you need to use IN.
Incorrect syntax near ',' is a generic error message which just indicates the problem in syntax before that deimilter. Its not always due to above reason alone.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30421 Posts

Posted - 07/18/2013 :  02:19:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE	Orders.ProductVendorControl
SET	ConfirmedProdDate =	CASE @UpdateProduction
					WHEN 1 THEN GETDATE()
					ELSE ConfirmedProdDate
				END,
	ConfirmedProdBy =	CASE @UpdateProduction
					WHEN 1 THEN @UserID
					ELSE ConfirmedProdBy
				END,
	ConfirmedATDate =	CASE @UpdateAT
					WHEN 1 THEN GETDATE()
					ELSE ConfirmedATDate
				END
	ConfirmedATBy =		CASE @UpdateAT
					WHEN 1 THEN @UserID
					ELSE ConfirmedATBy
				END
WHERE	VendorID = @VendorIdLookup;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/18/2013 :  02:30:02  Show Profile  Reply with Quote
since its posted in 2012
you can even use IIF which is a short hand way of writing CASE


UPDATE	Orders.ProductVendorControl
SET	ConfirmedProdDate =	IIF(@UpdateProduction = 1, GETDATE() ,ConfirmedProdDate),
	ConfirmedProdBy   =	IIF(@UpdateProduction = 1, @UserID , ConfirmedProdBy),
	ConfirmedATDate   =	IIF(@UpdateAT = 1, GETDATE() , ConfirmedATDate),
	ConfirmedATBy     =	IIF(@UpdateAT = 1, @UserID , ConfirmedATBy)
WHERE	VendorID = @VendorIdLookup;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52326 Posts

Posted - 07/18/2013 :  02:31:12  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

UPDATE	Orders.ProductVendorControl
SET	ConfirmedProdDate =	CASE @UpdateProduction
					WHEN 1 THEN GETDATE()
					ELSE ConfirmedProdDate
				END,
	ConfirmedProdBy =	CASE @UpdateProduction
					WHEN 1 THEN @UserID
					ELSE ConfirmedProdBy
				END,
	ConfirmedATDate =	CASE @UpdateAT
					WHEN 1 THEN GETDATE()
					ELSE ConfirmedATDate
				END,
	ConfirmedATBy =		CASE @UpdateAT
					WHEN 1 THEN @UserID
					ELSE ConfirmedATBy
				END
WHERE	VendorID = @VendorIdLookup;



N 56°04'39.26"
E 12°55'05.63"



fixed typo

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Edited by - visakh16 on 07/18/2013 02:31:47
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000