New answers tagged

0

Building on Paul White's example, to avoid worrying about the following warning without doing a SET ANSI_WARNINGS OFF: Warning: Null value is eliminated by an aggregate or other SET operation. You can do it with SUM and CASE like this: --Set the target table details here DECLARE @Schema sysname = 'dbo', @Table sysname = 'TableName' DECLARE ...


3

An alternative that does not use a cursor: -- Set the target table details here DECLARE @Schema sysname = N'Production', @Table sysname = N'Product', @SQL nvarchar(max) = N'SELECT '; DECLARE @QName sysname = QUOTENAME(@Schema) + N'.' + QUOTENAME(@Table); SET @SQL += ( SELECT NCHAR(13) + NCHAR(9) + QUOTENAME(C.name + ...


1

Yes. But if it's a one-time operation, you can instead hardcode it by dragging the "Columns" label from the Object Explorer in SSMS into the Query window, which will populate the list of columns into your query. You'd only go the dynamic route if there was absolutely no other option (and unwillingness to do updates when the schema changes isn't a valid ...


1

Let's suppose for country_name you wanted to do a leading and trailing wildcard search. You don't need dynamic SQL for this. Just: CREATE OR REPLACE FUNCTION report_get_countries_new (_pattern text) RETURNS SETOF lookups.countries AS $func$ SELECT * FROM lookups.countries WHERE country_name LIKE '%' || _pattern || '%' $func$ LANGUAGE sql; ...



Top 50 recent answers are included