2 updates within 1 query window...

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

All Forums

SQL Server 2012 Forums

Analysis Server and Reporting Services (2012)

2 updates within 1 query window...

Author

Topic

Cowski
Starting Member

30 Posts

Posted - 2013-07-17 : 08:03:13

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-17 : 11:11:59

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

Cowski
Starting Member

30 Posts

Posted - 2013-07-17 : 11:44:46

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!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 01:36:27

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-07-18 : 02:19:01

[code]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;[/code]

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 02:30:02

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-07-18 : 02:31:12

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources