Can't run query if defining temp table

OHPRS Admin 211 Reputation points
2024-11-04T17:42:24.9766667+00:00

I have the following T-SQL: 

alter procedure myProc_sp
AS
BEGIN

create table #myTable (
	retireeId  varchar(12)
)

select getDate()
return
END

With the 'create table' statement in there, it returns zero rows. If I comment out the 'create table' it returns the current date/time. I've also tried 'declare @myTable table()'. I've been creating stored procedures with 'create table' and 'declare table()' for years w/o problem. I've tried running some of those other procedures just now an they run just file, so I don't think this is a privilege issues on the part of the user. Just in case, I've gone ahead and [re]granted create table priv to the public user.

What am I doing wrong?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,961 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Naomi Nosonovsky 7,856 Reputation points
    2024-11-04T17:51:39.0166667+00:00

    What is your SQL Server version? Is there more to the procedure? I just tried it in SQL 2019 and it worked just fine, the date was returned.

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.