so confused - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

so confused

Author

Topic

Huascar82
Starting Member

6 Posts

Posted - 2007-12-15 : 12:05:15

OK, so I'm new to SQL server, which I'm sure you'll all see from my question below.

I am trying to migrate an access DB with queries over to sql server 2005. simple queries I can handle, but I've come accross a query that calls another query and does an update based off of my first query. The below queries work perfectly fine in access but I dont know how to get this going in SQL server. From my VERY minimal understanding in of SQL server i thought we couldnt call stored procedure (query1) and have it update the underlying tables. If I'm wrong, please show me how its done, If I'm right please show me the right way of doing this.
If you see spelling errors in the queries please ignore, that is not the full queries, it is just a cut down version to explain what I need to be able to do.

Query1


SELECT table1.assettag, table1.City, table2.Status, table2.ScheduleItems 
FROM Table1 Inner join on table1.assettag = table2.assettag 
where Status = "Scrubbed" or Status = "Initial"

Query2


Update Query1
SET query1.ScheduledItems = True
Where query1.Status = Scrubbed

thank you for any information or help.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-15 : 12:37:55

Update Table2
Set ScheduledItems = 'True'
FROM Table1 inner join Table2 on Table1.assettag = table2.assettag
WHERe Status = 'Scrubbed'

Poor planning on your part does not constitute an emergency on my part.

Huascar82
Starting Member

6 Posts

Posted - 2007-12-15 : 18:57:33

yeah, I was thinking I would have to incorporate both queries into one, but I wanted to see if there was a way of calling saved queries and use those results like in Access.
If there is a way, can someone explain it? I'll go ahead with DGs suggestion but I was hoping SQL Server had something like Access.

Thanks for the help DG.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2007-12-15 : 19:22:09

YOu can set up a VIEW which is the equivalent of an Access query, then just use the VIEW in your FROM clause, but you would still have to do the join and connect to the view anyway.

You can also set up functions that return tables, and use those tables to update records.

Why do want advice on how turn 1 step into multiple steps.

You can set up a stored procedure to accept parameters and update records using the variables?


Create proc pUpdateStatus (@status varchar(20), @TrueFalse varchar(5))
as
Update Table2
Set ScheduledItems = @TrueFalse
FROM Table1 inner join Table2 on Table1.assettab = table2.assettag
Where status = @status

Executing the referenced sample like so:



Exec pUpdateStatus ('Scrubbed','True')

There are many, many ways to do things in SQL, and hoping for equivalent functionality as compared to Access is like hoping Jeopardy gets as smart as Wheel of Fortune.

Poor planning on your part does not constitute an emergency on my part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources