How to Change AutoNumber Not Start From 1

I have a table with structure below :
Table_A
{
PGradeID int IDENTITY(1,1),
PName varchar(20)
}

If I delete all records on Table_A, and then fill some records,
PGradeID will no longer start from 1 anymore, everybody knows that. How
to change it so PGradeID will start from 1 after all records was
deleted?

Thanks b4

ResantLookup the DBCC CHECKIDENT command. Also, if you TRUNCATE rather than DELETE
then the IDENTITY will be reset to seed.

Why do you care what the IDENTITY value is? It's usually fatal to attach any
significance to the value assigned by IDENTITY. If you care about the value
then IDENTITY isn't the right solution.

--
David Portas
SQL Server MVP
--|||Reset identity value will not cause problem in my case, but I
appreciate your warning.
Thanks a lot, it's works!

How To Catch Output from SP / Function

I want to catch
the resultset from a stored procedure
or
a table output parameter from a stored procedure
or
a table output from a function
into
a varchar variable.
Any tip?
Not possible?
(it is for mailing the result from a query)
/k"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
You cannot have a table output parameter from a stored procedure

> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
Not Possible.
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
>I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||You cannot do that.
You can catch the output from a SP into a #Table. Thats the max you can do.
HTH,
Vinod Kumar
MCSE, DBA, MCAD, MCSD
http://www.extremeexperts.com
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
"kurt sune" <apa@.apa.com> wrote in message
news:uNdetTxGFHA.2616@.tk2msftngp13.phx.gbl...
> I want to catch
> the resultset from a stored procedure
> or
> a table output parameter from a stored procedure
> or
> a table output from a function
> into
> a varchar variable.
> Any tip?
> Not possible?
>
> (it is for mailing the result from a query)
> /k
>|||On Fri, 25 Feb 2005 09:35:12 +0100, kurt sune wrote:
(snip)
>Any tip?
>Not possible?
Hi Kurt,
Roji and Vinod already informed you that what you want is not possible.

>(it is for mailing the result from a query)
But if that's what you want, then you should check out xp_sendmail in
Books Online.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to CASE a SmallInt to a Varchar value

Why does this syntax work for bit but not for smallint ?
-- This works OK - Setting = BIT
CASE (dbo.tblAssessment.Setting)
WHEN 1 THEN 'Internal'
WHEN 0 THEN 'External'
END AS Setting,
-- This fails - error converting value 'N/A' to column of datatype smallint
-- Credit = SMALLINT
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN 'N/A'
ELSE dbo.tblAssessment.Credit
END AS Credit,
Thanks.Hi
It is expecting to return a smallint as one of the ELSE's in a smallint.
CASE (dbo.tblAssessment.Credit)
WHEN 101 THEN CONVERT(CHAR(10), 'N/A' )
ELSE CONVERT(CHAR(10), dbo.tblAssessment.Credit)
END AS Credit
Regards
Mike
"hals_left" wrote:

> Why does this syntax work for bit but not for smallint ?
>
> -- This works OK - Setting = BIT
> CASE (dbo.tblAssessment.Setting)
> WHEN 1 THEN 'Internal'
> WHEN 0 THEN 'External'
> END AS Setting,
> -- This fails - error converting value 'N/A' to column of datatype smallin
t
> -- Credit = SMALLINT
> CASE (dbo.tblAssessment.Credit)
> WHEN 101 THEN 'N/A'
> ELSE dbo.tblAssessment.Credit
> END AS Credit,
> Thanks.
>

How to capture return value from 'execute'

declare @.table varchar(100);

declare @.q varchar(100);

declare @.key bigint;

select @.table = 'key_table';

select @.q = 'select key from ' + @.table;

select @.key = exec(@.q); -> not working.

Check in books online; you'll find that the string execute version of the EXEC command does not provide the same ability to capture a return value as does the execution of a stored procedure. Sorry.|||

In that case, can you suggest an alternative for the my requirement. I want to get the return value of a select statement constructed dynamically.

Thanks,

|||

create a temp table and use this type

insert into #tempTable

exec ( @.yourExecString )

Also, understand that using the EXEC ( @.yourExecString ) syntax might leave you subject to SQL INJECTION attacks.

|||

Use sp_executesql instead EXEC(...). You can use output paraeters with this sp.

declare @.table sysname;

declare @.q nvarchar(100);

declare @.key bigint;

select @.table = N'key_table';

select @.q = 'select @.key = key from dbo.[' + @.table + N']';

exec sp_executesql @.q, N'@.key bigint output', @.key output;

select @.key

go

Be careful with sql injection.

The Curse and Blessings of Dynamic SQL

http://www.sommarskog.se/dynamic_sql.html

AMB