How To Script

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the table
name is dbo.item. What would my script look like to do this in SQL server
2005? The column has a silver key by it too in SQL Server Management
Studio.

ThanksBrian (b.houghtby@.eaglecrusher.com) writes:

Quote:

Originally Posted by

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
table name is dbo.item. What would my script look like to do this in
SQL server 2005? The column has a silver key by it too in SQL Server
Management Studio.

UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||What does the silver key mean?

"Erland Sommarskog" <esquel@.sommarskog.sewrote in message
news:Xns9947EFB6764B9Yazorman@.127.0.0.1...

Quote:

Originally Posted by

Brian (b.houghtby@.eaglecrusher.com) writes:

Quote:

Originally Posted by

>I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
>table name is dbo.item. What would my script look like to do this in
>SQL server 2005? The column has a silver key by it too in SQL Server
>Management Studio.

>
UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

|||What does the silver key mean?

It means that it is in a foreign key relationship to another table.

On Jun 7, 9:51 am, "Brian" <b.hough...@.eaglecrusher.comwrote:

Quote:

Originally Posted by

>
"Erland Sommarskog" <esq...@.sommarskog.sewrote in message
>
news:Xns9947EFB6764B9Yazorman@.127.0.0.1...
>
>
>

Quote:

Originally Posted by

Brian (b.hough...@.eaglecrusher.com) writes:

Quote:

Originally Posted by

I need to update a value in IMA_GLInvAcctNbr from 11283 to 11500. the
table name is dbo.item. What would my script look like to do this in
SQL server 2005? The column has a silver key by it too in SQL Server
Management Studio.

>

Quote:

Originally Posted by

UPDATE dbo.item
SET IMA_GLInvAcctNbr = 11500
WHERE IMA_GLInvAcctNbr = 11283

>

Quote:

Originally Posted by

--
Erland Sommarskog, SQL Server MVP, esq...@.sommarskog.se

>

Quote:

Originally Posted by

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...downloads/books...
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ons/books.mspx- Hide quoted text -

>
- Show quoted text -

How to retrieve value from Exec stmt?

hi, all..
the following is part of my sp
I want to know how to assign result of stmt.1 to @.tp

declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
Exec ('SELECT MAX(UpdateTime) FROM ' + @.tableName) -- stmt.1

thank you..Try putting everything in an EXEC statement:

EXEC('
declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = ''tblState''
SELECT MAX(UpdateTime) FROM @.tableName
')

I can't say this is the optimal way to do it, but it should work. Note: Those are two single quotes around tblState, not double quotes.|||declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
EXEC sp_executesql 'SELECT @.tp=MAX(UpdateTime) FROM ' + @.tableName, N'@.tp datetime OUTPUT',@.tp OUTPUT

select @.tp

it should give u the exact result.|||Thank you for reply,
however it gives following err...
Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '+'.|||Modifying the previous example, I'd use:declare @.sql VARCHAR(250)declare @.tp datetime
declare @.tableName varchar(100)
SET @.tableName = 'tblState'
SET @.sql = 'SELECT @.tp=MAX(UpdateTime) FROM ' + @.tableName

EXEC sp_executesql @.sql, N'@.tp datetime OUTPUT',@.tp OUTPUT

select @.tp-PatP|||USE Northwind
GO

SET NOCOUNT ON

DECLARE @.tp datetime, @.TABLE_NAME sysname, @.COLUMN_NAME sysname, @.SQL varchar(8000)

SELECT @.TABLE_NAME = 'Orders', @.COLUMN_NAME = 'OrderDate'

CREATE TABLE #x(tp datetime)

SET @.SQL = 'INSERT INTO #x(tp) SELECT MAX('+@.COLUMN_NAME+') FROM '+@.TABLE_NAME

EXEC(@.SQL)

SELECT @.tp = tp FROM #x

SELECT @.tp

DROP TABLE #x

SET NOCOUNT OFF
GO|||Thank you all..
All are good hint..
Now it works..

my version is following..

DECLARE @.SQLString NVARCHAR(500), @.tbl nvarchar(100)
DECLARE @.ParmDefinition NVARCHAR(500)
declare @.tp datetime
SET @.tbl = 'tblState'
SET @.SQLString = N'SELECT @.tp = MAX(UpdateTime) FROM ' + @.tbl
SET @.ParmDefinition = N'@.tp datetime OUTPUT'
EXECUTE sp_executesql @.SQLString, @.ParmDefinition
,@.tp OUTPUT
Select @.tp as UpdateTime